This is a very quick tutorial to help people avoid SQL injection with their PHP scripts. It seems all too common that people are writing PHP scripts without considering the fact that someone could easily inject some malicious SQL code that could wreak havoc on an entire Web site.

To put it very simply, for those of you that don’t know what SQL injection is; it’s basically sending SQL code through a script that causes the query to execute unintended commands. Some very good examples of SQL injection can be found in the Wikipedia article.

Here are a few very quick tips to help you avoid SQL injection. Of course, nothing is foolproof, but this should take you a long way.

1) If your input is supposed to be specific type, make sure you check its type. When using PHP with MySQL, there are not a whole lot of types you can really deal with. Pretty much, you’re either dealing with numeric information or string information. Technically, you can also have binary information in a MySQL database, but it’s not quite as easy to check for as it is to check if something is a numeric value.

So, if you are getting ready to execute an SQL query through PHP that is expecting a numeric input, like the query below; you should always check to make sure your value is numeric.

$sql = "SELECT * FROM table WHERE field=".$myvalue;

Assuming that $myvalue is supposed to be a numeric value, you should check that, like such:

if(is_numeric($myvalue)) {
$sql = "SELECT * FROM table WHERE field=".$myvalue;
}

else {
die("You did not specify a numeric value");
}

With that little bit of code, we’ve now verified that $myvalue is, in fact, a number before running our query.

2) Always escape your input

PHP has quite a few functions built in that allow you to escape your data properly for the database with which you’re interacting. The two that deal with MySQL are mysql_escape_string and mysql_real_escape_string. The main difference between the two is that the mysql_real_escape_string function will actually escape the string specifically for the database connection you currently have open.

Apparently, the different engines on which you can build MySQL databases actually need slightly different escape methods, which is why the mysql_real_escape_string function should be used whenever you have a database connection open.

The mysql_escape_string function will generically escape the string for standard MySQL input.

My recommendation would be this:

Any time you run a dynamic SQL query, whether the data will ever be inputted by the user or not, make sure you escape that data.

Any SQL query you ever run should look something like:

$sql = "SELECT * FROM table WHERE field='".mysql_real_escape_string($value)."'";

or

$sql = “SELECT * FROM table WHERE field=’”.mysql_escape_string($value).”‘”;

In my opinion, there are only two instances in which you should ever use mysql_escape_string instead of mysql_real_escape_string:

1. If you are building the query prior to opening a connection to the database, for some reason.
2. If you are building a query based on the results of another query that was connected to a different database; and, therefore, your link resource identifier is different.

Again, it’s not foolproof, and it’s not really even that in-depth; but these are some very simple, great ways to help protect your site against possible SQL injection.

I hope that helps some people. Just remember - don’t ever send unescaped data to your database!