SQL Injection

alirezaok

how do i safe my script code againest sql injection attack?
i use of simple code

as example

// $id requested from url
$query = mysql_query("SELECT * FROM `data` where id=$id"); 
....

is it safe?
is php safe for sql injection?

Piranha

I will try to answer all the questions. First question to answer is that your query is not safe. The first example is an example of how you intend to use it, the second how it could be used.

$id = 5;
$query = mysql_query("SELECT * FROM `data` where id=$id");
//the statement sent to mysql:
SELECT * FROM `data` where id=5

But in the following example it shows that your code is not safe:

$id = "5; DELETE FROM `data`;";
$query = mysql_query("SELECT * FROM `data` where id=$id");
//the statement sent to mysql:
SELECT * FROM `data` where id=5; DELETE FROM `data`;

It will first search exactly as you wanted it to do, but after that it will totally erase your whole data table.

It seems that it's not safe to use, but there are ways around the above problem. If you first do the sql syntax that require strings as input:

$query = mysql_query("SELECT * FROM `data` where id='$id'");

Then you make sure that you use mysql_escape_string() on everything you get into the webpage:

$id = "5; DELETE FROM `data`;";
$id = mysql_escape_string($id);
$query = mysql_query("SELECT * FROM `data` where id='$id'");
//the statement sent to mysql:
SELECT * FROM `data` where id='5; DELETE FROM `data`;'

By searching as a string (using the ' signs) you make sure that the above statement is searching for a id that is "5; DELETE FROM data;".

$id = "5'; DELETE FROM `data`;";
$id = mysql_escape_string($id);
$query = mysql_query("SELECT * FROM `data` where id='$id'");
//the statement sent to mysql:
SELECT * FROM `data` where id='5\'; DELETE FROM `data`;'

mysql_escape_string() changes the ' sign to \', and by doing that it makes sure that the database don't read it as a stop of the search. This will make it safe, to my knoledge as safe as it is possible to do in PHP.

Piranha

To add to the post above: Some people don't like the idea of using strings where it's not needed, they would prefer to use it as a int if it is a int. I hope that someone that does it this way can post and tell how they make sure that their database is handled to be safe.

cahva

BTW, this does not work in PHP:

SELECT * FROM data where id=5; DELETE FROM data;

PHP would give you error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; ....

You can only do one query at a time. ";" is ignored so those kind of injections are not possible. But those queries which have a DELETE.. Be careful with them.

MarkR

The best way of protecting your application against SQL injection attack is to use parameterised queries throughout your application.

The mysql_ functions don't support this natively, however if you use PEAR DB, you can use them. Additionally, if you use mysqli or PDO, these natively support MySQL prepared queries.

Then it would look like:

// Assume $db is a PDOConnection object
$sth = $db->prepare("SELECT * FROM data where id=?"); 
$sth->execute(array($id)); // Execute it with parameter
// TODO: fetch results...

And that's guaranteed to be invulnerable to SQL injection.

Moreover, as you're expecting it to be an int, you could do

$id = (int) $id;

First anyway to cast it into an int. This will stop any string the user has attempted to inject getting through. However, this is not a good general solution as it won't work for string data.

Don't attempt to escape every string correctly in SQL; instead just use parameterised queries throughout your entire app. It works for me.

Mark

alirezaok

Cahva,

so you say php is safe against sql injection? because only one query is possible at a time?
is there other way for sql injection?

Piranha,
what is diffrence between mysql_escape_string and addslach? i could not found diffrence.
and if magic_quotes_gpc() is on, it is not to nessery to use of mysql_escape_string?

Thanks

Piranha

Ok, I didn't know that. It seems like I was wrong about that thing, but it could still be used in a way that is not intended like below:

$id = "5 OR 1 = 1";
$query = mysql_query("SELECT * FROM `data` where id=$id");
//the statement sent to mysql:
SELECT * FROM `data` where id=5 OR 1 = 1

It will return everything from the datatable. In some cases it's no big deal, but in others it's a very big deal. Lets pretend that the data table is the table with usernames and (plain) passwords, then it might reveal all users and passwords. Or pretend that you have a table where invoices are stored. Then I can use this way to see your invoices.

Or lets see another simple example

$id = 1;
$name = "Kalle";
$sql = "UPDATE data SET name = '$name' WHERE id = $id";
// The sql will be
UPDATE data SET name = 'Kalle' WHERE id = 1

This would do exactly what is intended. But the example below won't do it as intended:

$id = "1 OR 1 = 1";
$name = "Kalle";
$sql = "UPDATE data SET name = '$name2' WHERE id = $id;
// The sql will be
UPDATE data SET name = 'Kalle' WHERE id = 1 OR 1 = 1

1 = 1 is always true. The result is that name in all rows will be set to "Kalle"

Piranha

alirezaok wrote:
Piranha,
what is diffrence between mysql_escape_string and addslach? i could not found diffrence.
and if magic_quotes_gpc() is on, it is not to nessery to use of mysql_escape_string?

Sorry, but I can't help you with those questions. In the manual it says a little about escaping strings, and that is basically all I know about it.

cahva

Well the difference is that if you put data to table with addslashes() the slashes are actually put to database and you would have to use stripslashes() function when retrieving data. But with mysql_real_escape_string you can safely put data(with ' or " characters) and they will be the same in the database. So stripslashes is not needed when getting data from mysql.

alirezaok

thank you cahva
one people helped me wrong! see: http://phpbuilder.com/board/showthread.php?t=10326057

and thank you Piranha for good example

MarkR wrote:

// Assume $db is a PDOConnection object
$sth = $db->prepare("SELECT * FROM data where id=?"); 
$sth->execute(array($id)); // Execute it with parameter
// TODO: fetch results...

there is not the pear DB in all web servers and user must upload it.

MarkR wrote:
$id = (int) $id;

about your second soloution i found a good class : http://cyberai.com/inputfilter/

thank you

alirezaok

is it better magic_quotes_gpc() always be on instead of mysql_real_escape_string ?

ahundiak

alirezaok wrote:
is it better magic_quotes_gpc() always be on instead of mysql_real_escape_string ?

No. magic_quotes_gpc() should be disabled. It escapes everything even stuff that might not be destined for the database. Furthermore, different types of databases require different escaping functions so it just adds to your work. magic_quotes_gpc() will be disabled by default in later versions of php. It's one of those evil hold overs (like register_globals) from the early days of php.

Use mysql_real_escape_string as you build your mysql queries. Or, as previously suggested, use prepared statements.

NogDog

You can use get_magic_quotes_gpc() to dynamically handle whether or not magic_quotes are turned on:

function sanitize_input($input)
{
  if(get_magic_quotes_gpc())
  {
    $input = stripslashes($input);
  }
  return(mysql_real_escape_string($input));
}

ahundiak

I think it's easier to just add:
php_flag magic_quotes_gpc off

To .htaccess (or edit php.ini) and be done with it.

NogDog

ahundiak wrote:
I think it's easier to just add:
php_flag magic_quotes_gpc off

To .htaccess (or edit php.ini) and be done with it.

That's OK for private scripts, but not so good for anything you might want to sell or make available as open source to users who either can't or don't want to change their magic_quotes setting.

alirezaok

may you explain me why? :

if(get_magic_quotes_gpc()) 
  { 
    $input = stripslashes($input); 
  }

i thing when magic is enable it escaped with a backslash automatically and dont need to stripslashes(). right?

NogDog

alirezaok wrote:
may you explain me why? :
if(get_magic_quotes_gpc()) 
  { 
    $input = stripslashes($input); 
  } 
i thing when magic is enable it escaped with a backslash automatically and dont need to stripslashes(). right?

The idea in my code was that I'm going to use mysql_real_escape_string() to make the input safe for SQL use. In that case, I do not want to have it re-escaping any back-slashes which magic_quotes has already inserted into the string to escape quotes and back-slashes. So I'm using get_magci_quotes_gpc() to see if magic quotes is turned on, and if so stripping out the slashes it inserted prior to sending it through mysql_real_escape_string(), which will then only be escaping the actual user input. See Example 3 on this page for a more thorough example (and possilby better explanation).