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:
- If you are building the query prior to opening a connection to the database, for some reason.
- 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!
6 Responses
[…] directly from the user). Check out my latest blog entry to see a little bit about why and how: Avoiding SQL Injection with PHP | HTMLCenter – Web Help and Web Development Blog I’ve taken the liberty of escaping your data in the code I provided above. 2) Your UPDATE query is […]
Hi, I’m quite new to PHP and SQL and very new in escaping strings for bad data.
I have a question for you witch concerns my site that is being build. (am building a text based mmo)
I have a lot of interaction with the mysql database.
I want to use a system that reports “events” to the user that is doing actions in the site. First I used a simple table without width and height settings. This worked well, but gave a result that I am not looking for, so I wanted table width and cell height settings. For this to work I would need to escape the input, with a command, or on the fly with the symbol .
Here’s a sql update that I am using (don’t kill me for the style it’s in.)
mysql_query(“UPDATE myphpgame_player SET storedevents = ‘Kill event, $today You killed $targetplayer.$storedevents’ WHERE uid = ‘$uid'”);
For using cell height I changed this into:
mysql_query(“UPDATE myphpgame_player SET storedevents = ‘Kill event, $today You killed $targetplayer.$storedevents’ WHERE uid = ‘$uid'”);
And escaped the ‘ ‘ symbols with and . This went fine for ONE query.
The second time the query would run it would go stuck on $storedevents. since it has unescaped data like so I think I need to escape the data in $storedevents.
This data comes from another command like:
$sql002 = “SELECT location,life,rank,experience,storedevents,kills,gangstername,newevent,wallet,off,def,stl,gameturns FROM myphpgame_player WHERE uid=’$uid'”;
$result002 = mysql_query($sql002) or die(mysql_error());
if(mysql_num_rows($result002) == 0)
{
echo “else if no one screwed up, this message never shows.”;
}
while(list($ownloc,$ownlife,$ownrank,$ownexp,$storedevents,$ownkills,$owngangstername,$ownnewevent,$ownwallet,$ownoff,$owndef,$ownstl,$owngameturns) = mysql_fetch_row($result002))
…..
My question is how to escape the data that was put in with the first query? the results of the query would go in $storedevents.
I cannot seem to post html table tags!
I changed it into:
mysql_query(“UPDATE myphpgame_player SET storedevents = ‘Kill event, $today You killed $targetplayer.$storedevents’ WHERE uid = ‘$uid'”);
(trying spaces to be able to show the query..)
didn’t work. what a nice site…. :( to bad can’t ask a question this way.
mysql_query(“UPDATE myphpgame_player SET storedevents = ‘Kill event, $today You killed $targetplayer.$storedevents’ WHERE uid = ‘$uid'”);
asterix symbols then *