Best Practices to Prevent SQL Injection

[kuau]

I would like to know the definitive way to guard against sql injection attacks so that I can incorporate these protections into my code, but I am a bit unsure as to which are the best commands to use. Do you have to use them all, or is one sufficient? I've never had a complete grasp of what "escaping" means, so could someone please illustrate the best way to safely add data to a database? I've seen things like below, which would imply that you could thereafter refer to the variable as $var, but I have found this not to be the case:

$var = mysql_real_escape_string($_POST['var']);
$var = htmlspecialchars($_POST['var']);

Do you add data like this?...

INSERT INTO location ( location, address, city)
VALUES (	'" .$_POST['location']."', '".$_POST['address']."', '".$_POST['city']."' )";

or like this?...

$location = mysql_real_escape_string($_POST['location']);
$address = htmlspecialchars($_POST['address']);
$city = mysql_real_escape_string($_POST['city']);

VALUES (	'" .$location."', '".$address."', '".$city."' )";

or what???

Thanks.

[djr33]

mysql_real_escape_string() is a function that escapes the characters so they cannot affect the syntax of a mysql statement.

That is all you need for security.

However, you might want to use html_entities() also, if you want, for example, to later output it as just text on an html page and not have any of the html affected.

Escaping is a general term for "escaping" (deactivating, removing affect of) characters that would work in a string to cause problems.

The simplest example is:
echo 'You can't do this';
echo 'Now, you aren\'t going to have problems.';

So, escaping works differently for every language. In mysql it is especially dangerous, because the syntax of mysql is not written directly, but rather AS A STRING. That's why you need to be careful with it. If you use eval() in php, which runs a string like php code, then you need to be just as careful.

Anyway, for mysql, mysql_real_escape_string() will be all you need.

For anything else, you'll need another method specific to that.



Your second method, escaping first, is MUCH safer.

The other method would allow someone to send, as post data, this:
something; delete database 'info';

And your data would all be gone. (The first semicolon ends the first statement, whatever you were doing in your sql statement, and the second deletes the database.)

mysql_real_escape_string() will convert any problematic characters to just strings. In this case, you'd have the semicolons and apostrophes fixed.

[techietim]

something; delete database 'info';

mysql_query() only lets you do one query at a time, so that would be disabled by default.

mysqli:: prepare() should do you good. Make sure you read up on it though.

[djr33]

http://www.php.net/manual/en/functio...ape-string.php

There are some examples on the page, then.

In the case of mysql_query, it seems like that limitation does provide some level of security, but it's certainly vulnerable, if a bit more work to attack.

I'm not sure about your suggestion of mysqli::_prepare()-- it's for mysqli, not mysql, which may not be compatible with the script in question. I'm not sure if it requires mysqli or will escape any sql, though it looks like it requires a mysqli conn to work, anyway.

However, assuming it works, what is the advantage over mysql_real_escape_string()?

[jackbenimble4]

If you need MySQL instead of mysqli, I use the PDO class with a MySQL driver, and then used prepared statements.

As far as I'm aware, there's no possible way for an SQL injection attack when you use prepared statements. The idea is that you tell the MySQL engine ahead of time what the query you want to execute is, except without any values that you're receiving from the user. Then when you execute the query, you pass along the values and they're inserted in the query you already prepared. There's no way to meddle with the query because the MySQL engine already knows what the query is.

PDO Extension
PDO:: prepare

[kuau]

Thank you so much for the thorough explanation and links. I was able to follow the mysql_real_escape_string() part but I have to admit that the PDO:: prepare really lost me. I need to do a lot of background reading before I can tackle that method.

Do you have to use mysql_real_escape_string() on date and integer values as well, or just strings?

Thanks, e

[Twey]

Depends where you get them from and what format they're in (in the case of the dates). If you're getting them from the user, then some validation has to be done — but checking isnumeric() will work just as well if the input is meant to be a number. The date is more complex: perhaps try converting it using strtotime() (which will return false if it fails due to an invalid date).