[SOLVED] sql injection

[Lambneck]

whats the easiest way to protect against it?

[DarkWater]

"easy" and "security" don't often belong in the same discussion, just to let you know.  I'd start with mysql_real_escape_string().

[Lambneck]

do you think mysql_real_escape_string()

as effective as prepared statement queries?

can they be used together?

 

I just started reading about sql injection so dont know much about it.

if mysql_real_escape+string() is still  vulnerableto attack based on bad Unicode

characters even if the user's input is being escaped

is there a more effective way or a combination of ways

to more effectively protect against attacks?

 

[waynew]

Prepared statements are better but I've heard of no such vulnerability with mysql_real_escape_string. Are you sure the information you got isn't outdated?

[.josh]

The only "more" effective way is to not allow/use user input at all.  Or at least, nothing that's not predefined from some dropdown or something.  Prepared statements are secure in that once you prepare a statement, you can't change it (hence no injection later on).  mysql_real_escape_string is secure in that it escapes quotes, which is what 99.9999% of sql injection is.  

[waynew]

If you have a query like this:

 

$data = mysql_real_escape_string($data);
mysql_query("SELECT id FROM example_table WHERE name = '$data'");

 

As far as I know; they wouldn't be able to break out of that, seeing as the data is encapsulated in quotes and mysql_real_escape_string doesn't allow quotes in in order to break free from those quotes. (Correct me if I'm wrong).

 

Do proper checks on the data coming in.

 

Is it numeric? ETC. Along with mysql_real_escape_string and you should be fine. As far as I know, the only vulnerability with mysql_real_escape_string is present when you're changing between character sets.

[DarkWater]

The only "more" effective way is to not allow/use user input at all.  Or at least, nothing that's not predefined from some dropdown or something.  Prepared statements are secure in that once you prepare a statement, you can't change it (hence no injection later on).  mysql_real_escape_string is secure in that it escapes quotes, which is what 99.9999% of sql injection is.  

 

You can easily change dropdowns and stuff.

[.josh]

The only "more" effective way is to not allow/use user input at all.  Or at least, nothing that's not predefined from some dropdown or something.  Prepared statements are secure in that once you prepare a statement, you can't change it (hence no injection later on).  mysql_real_escape_string is secure in that it escapes quotes, which is what 99.9999% of sql injection is.  

 

You can easily change dropdowns and stuff.

 

Well yes you can, but dropdown+whitelist = ... ?

[waynew]

Whitelist ftw (Also, thanks Crayon for actually giving me the term for what I was doing).

 

My favourite is is_numeric(). I try my best to revolve my apps around numerical values as much as possible.

 

[Lambneck]

<?php
function check_input($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
  {
  $value = stripslashes($value);
  }
// Quote if not a number
if (!is_numeric($value))
  {
  $value = "'" . mysql_real_escape_string($value) . "'";
  }
return $value;
}

mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($database);

if (isset($_POST['Submit'])) 
{

$name = check_input $_POST['name'];
$email = check_input $_POST['email'];
$subject = check_input $_POST['subject'];
$message = check_input $_POST['message'];

$submission_date = date("l M dS, Y, H:i:s");
$ip = getenv ('REMOTE_ADDR');

mysql_query("INSERT INTO $table (col_2, col_3, col_4, col_5, submission_date, ip_address) VALUES ('$name', '$email', '$subject', '$message', '$submission_date', '$ip')")or die(mysql_error());

// escape username and password for use in SQL
$name = mysql_real_escape_string($name);
$email = mysql_real_escape_string($email);
$subject = mysql_real_escape_string($subject);
$message = mysql_real_escape_string($message);

    $user = $_POST['name'];

    echo '<table id=table1><tr><td><b>Thank you ' . $user . ', your information has been submitted.</b></tr></td>';

 

in the above example does the mysql_real_escape_string() appear redundantly?

or is this accurate?

[.josh]

yes.  You already sanitize them with it in your function so you don't need to turn around and do it again.  Not to mention you do it after your insert, which kind of makes it pointless.  Calling the function to sanitize them before the insertion is all that's needed.  Though, in your function, I'm kind of wondering why you are wrapping single quotes around the values... 

[Lambneck]

Im still new to this.

how should i write it if it is not wrapped in quotes?

 

[.josh]

In your function, change

 

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

 

to

 

$value = mysql_real_escape_string($value);

 

unless there was some reason you wanted your data to be wrapped in quotes.