[SOLVED] Smallest,simplest way to stop MYSQL injections.

[lynxus]

Hi guys,

Im looking for a easy way to stop SQL injections.

 

For instance. I have the below:

 

#Select DB

mysql_select_db("bar", $con);

 

#Select only the last 15 posts.

$result = mysql_query("SELECT * FROM foo WHERE siteid = $siteid ORDER BY unqid DESC LIMIT 15");

 

$siteid is taken from a $_GET[''];

 

So  from what i can tell people would put some random dodgy chars into the $siteid and mess up the SQL to output what they want or do even worse.

I also do roughly the same for inserting into my DB.

All vars are passed by a $_GET:

 

 

// Make a MySQL Connection

mysql_connect("localhost", "username", "password") or die(mysql_error());

mysql_select_db("bar") or die(mysql_error());

 

// Insert a row of information into the table "example"

mysql_query("INSERT INTO foo

(siteid, username, message, weburl, email) VALUES('$siteid', '$username', '$message', '$weburl', '$email' ) ")

or die(mysql_error());

 

__________________

From what i can tell, This is a nasty way of doing things.

 

Is there like a small function i could have that i could pass it a var and it would clean it but not messing up and normal text?

 

ie:

$var = StopHackers($var);

 

I dont understand some of these more complex ways of preventing injections so the simplest and smallest way would be awsome. ( But still did the job )

 

Thanks

G

[gevans]

mysql_real_escape_string()

 

That's your main port of call!!

 

Works after mysql_connect() has been run.

 

Escapes all the nasty characters people may try!

[lynxus]

mysql_real_escape_string()

 

That's your main port of call!!

 

Works after mysql_connect() has been run.

 

Escapes all the nasty characters people may try!

 

So i would try:

 

// Make a MySQL Connection

mysql_connect("localhost", "username", "password") or die(mysql_error());

mysql_select_db("bar") or die(mysql_error());

 

$siteid = mysql_real_escape_string($siteid);

$username = mysql_real_escape_string($username);

$weburl = mysql_real_escape_string($weburl);

$email = mysql_real_escape_string($email);

 

// Insert a row of information into the table "example"

mysql_query("INSERT INTO foo

(siteid, username, message, weburl, email) VALUES('$siteid', '$username', '$message', '$weburl', '$email' ) ")

or die(mysql_error());

 

 

 

Thanks

G

 

[gevans]

Exactly, make sure that is a $_POST['siteid'] if you're using post, and do it for all user contributed variables!

[PFMaBiSmAd]

For a numeric field (siteid in this example) escaping the data will have little effect, because you can include things like a UNION SHOW TABLES query after the number to get the first query to display all kinds of information about the database.

 

For numeric fields, you must either validate that the variable only contains a number or you must case it as a number so that any text is ignored.

 

Edit: Here is an example where a UNION SELECT ... query was injected following a number in a numeric field that display all the usernames and passwords in a table - http://www.codingforums.com/showthread.php?t=151150

[gevans]

That's true

 

$siteid = (is_numeric($_GET['siteid']))? $_GET['siteid'] : 0;

 

something like that!

[lynxus]

humm not sure what you mean about the number thing?

They can screw me over by just using numbers?

 

in this case, everything is sent via a GET not a POST.

 

Ive found this:

 

Would this protect me?

 

function check_input($value)

{

if (get_magic_quotes_gpc())

  {

  $value = stripslashes($value);

  }

 

if (!is_numeric($value))

  {

  $value = "'" . mysql_real_escape_string($value) . "'";

  }

return $value;

}

 

$con.......Blaaa

 

$username = check_input($_GET['username']);

$siteid = check_input($_GET['siteid']);

 

 

 

[premiso]

You should know what is to be sent to the form.

 

I would add another parameter to your check input "type"

 

<?php
function check_input($value, $type=0)
{
if (get_magic_quotes_gpc())
  {
  $value = stripslashes($value);
  }

switch ($type) {
   case 0: // string
       $value = "'" . mysql_real_escape_string($value) . "'";
   break;
   case 1: // int
       if (!is_numeric($value)) {
           $value = 0; // or error out whichever.
       }
   break; 
}
return $value;
}

$username = check_input($_GET['username']);
$siteid = check_input($_GET['siteid'], 1); // check for int

 

[lynxus]

You should know what is to be sent to the form.

 

I would add another parameter to your check input "type"

 

<?php
function check_input($value, $type=0)
{
if (get_magic_quotes_gpc())
  {
  $value = stripslashes($value);
  }

switch ($type) {
   case 0: // string
       $value = "'" . mysql_real_escape_string($value) . "'";
   break;
   case 1: // int
       if (!is_numeric($value)) {
           $value = 0; // or error out whichever.
       }
   break; 
}
return $value;
}

$username = check_input($_GET['username']);
$siteid = check_input($_GET['siteid'], 1); // check for int

 

 

 

 

I see, Humm.

The problem is although it *should* be a integer that's been sent, I cant be sure as someone could send it a string instead...

 

-G

[premiso]

Sure you can. If it is numeric, than this would work:

 

<?php
function check_input($value, $type=0)
{
if (get_magic_quotes_gpc())
  {
  $value = stripslashes($value);
  }

switch ($type) {
   case 0: // string
       $value = "'" . mysql_real_escape_string($value) . "'";
   break;
   case 1: // int
       if (!is_numeric($value)) {
           $value = 0; // or error out whichever.
       }else {
           $value = intval($value);
      }
   break; 
}
return $value;
}

$username = check_input($_GET['username']);
$siteid = check_input($_GET['siteid'], 1); // check for int

 

That way it is converted to an integer regardless.

[lynxus]

Ah right.

 

cool

Thanks guys

 

Ill mark as resolved.

Cheeeers