Protecting Your PHP/MySQL Queries from SQL Injection

October 21, 2007

Protecting Your PHP/MySQL Queries from SQL Injection

Filed under: MySQL, PHP — Matt @ 6:36 am

SQL injection is a serious concern for webmasters, as an experienced attacker can use this hacking technique to gain access to sensitive data and/or potentially cripple your database. If you haven’t secured your applications, I implore you to get yourself familiar with the following method and grind it into your coding routine. One unsafe query can result in a nightmare for you or your client.

I’ve read through a lot of guides, and they tend to over complicate this, so I’ll be as straight forward as possible. In PHP the easiest way is to pass your data through the mysql_real_escape_string function. By escaping special characters on fields where the user can manipulate the database, you will avoid being vulnerable. Take a look below at the example of what to do and what not to do.

// This is a vulnerable query.
$query = "SELECT * FROM products WHERE name='$productname'";
mysql_query($query);

// This query is more secure
$query = sprintf("SELECT * FROM products WHERE name='%s'",
mysql_real_escape_string($productname));
mysql_query($query);

Since I primarily code in PHP, I can’t confidently provide techniques for other programming languages. The most important part of protecting yourself is stopping users from being able to pass unaltered database manipulative special characters, like single quotes.

MSDN - SQL Injection Article
Wikipedia - SQL Inection
SecuriTeam - SQL Injection Walkthrough
SitePoint - SQL Injection Attacks, Are You safe?

31 Comments »

just a note…
Note: mysql_real_escape_string() does not escape % and _.
These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.
(from the php manual)

Comment by natural_sgf — October 21, 2007 @ 8:48 am

@narutal_sgf:
If you are doing wildcard queries you can escape %_ with the following function
http://ca3.php.net/manual/en/function.addcslashes.php

Comment by Matt — October 21, 2007 @ 8:56 am

Another safe way of performing MySQL queries in PHP is to use the included mysqli library (you can read up on all the functions at http://php.net/mysqli ):
$name = $_GET[’product’];

$db = new mysqli(”localhost”, “user”, “pass”, “database”);
if ($stmt = $db->prepare(’SELECT price FROM products WHERE name = ?’)) {
$stmt->bind_param(’s’, $name);
$stmt->execute();
$stmt->bind_result($price);
$stmt->fetch();

echo ‘The product costs $’, $price, ‘.’;

$stmt->close();
}
$db->close();

Comment by Andreas Blixt — October 21, 2007 @ 11:30 am

Thanks for this tip, I’m going to look closer at how my php websites are talking to mysql, in the past I’ve relied on things like mod_security and sudokin patch for php to protect things, but that’s not solving the root problem like you are here. I’m also designing a login system that I will implement this procedure into it when it talks to mysql.

Appreciate the post, thanks.

Comment by fak3r — October 21, 2007 @ 11:31 am

I suggest that you take a look at PDO (PHP Data Objects), the new generic PHP database interface. This interface supports prepared statements, which prevent SQL injections. You do not have to run php_real_escape_string() before the query if you use prepared statements.

http://www.php.net/manual/en/ref.pdo.php

Comment by Init — October 21, 2007 @ 11:35 am

Another simple yet effective way to protect yourself is to validate the user input using regular expressions AND using the mysql_real_escape_string().

In case you are not familiar with RE’s, one of their uses is that it allows you to accept only the character set that you specify.

Comment by Rodey — October 21, 2007 @ 12:16 pm

For added security you can also use prepared statements if you are in a php 5 environment.

Comment by Sam Jones — October 21, 2007 @ 1:40 pm

you should also use

mysql_query(”SELECT * FROM a LIMIT ” . (int)$limit);

instead of just

mysql_query(”SELECT * FROM a LIMIT ” . $limit);

(int) returns 0 if $limit contains anything that is not a number and cannot be converted into a number.

Comment by yarin — October 21, 2007 @ 1:45 pm

Thanks for the tip!

Comment by mark — October 21, 2007 @ 1:58 pm

This article is good one.

Following article i find useful as well about SQL injection.

http://www.dev-exchange.com/cms_view_article.php?aid=10

Comment by James — October 21, 2007 @ 2:46 pm

It’s also really important to mention that mysql_real_escape_string() is useless if you don’t put your variables in quotes.

Comment by will — October 21, 2007 @ 2:55 pm

You will also want to do a check to see if the server has magic_quotes_gpc() going, if its on and you real_escape_string something or even addcslashes, it just makes for ugly things in databases.

ONLY USE REAL_ESCAPE_STRING OR ADD(C)SLASHES IF magic_quotes_gpc() is FALSE!!!

Comment by Jhecht — October 21, 2007 @ 4:29 pm

Use Creole or another Database(-Abstraction)-Lib that does this for you.

Comment by Erdung — October 21, 2007 @ 9:52 pm

I use a roll your own version, which has similar syntax to prepared statement.

All those who are interested in a similar (but different) take head over to my site

Comment by Tristan — October 22, 2007 @ 12:42 am

It might be good to note too that mysql_real_escape_string helps to protect you from sql injection, but it doesn’t help much against other script hacks if you don’t check your input data and use strip_tags where necessary. You can escape scripts while entering them into the database all you want, but once you print them to screen they will get run just the same.

Comment by Brandon — October 22, 2007 @ 2:25 am

I use my own version in ASP+MySQL. It just strips offending junk out of the query string.

Also, I have my own functions instead of just using Request( “var” ) - this helps protect against SQL injection and also allows me to provide a default. e.g.
var = GetRequestInt( “var”, 0 )

The above ensures it’s a number and also gives me a default should the param not be present.

Comment by Russ @ bombay potatoes — October 22, 2007 @ 5:02 am

Some may consider it overkill, but i use a function that i wrote myself, and that checks all input strings for sql keywords, in executable order, using a Regular Expressions

eg.

“I like you” would work fine, but

“where x like y” wouldn’t work.

It’s alot more work initially, but i believe its secure, am i right to think this?

Comment by Jonathan — October 22, 2007 @ 9:09 am

How would you check to see if it is secure?

Comment by aaron — October 22, 2007 @ 9:31 am

mmm, similar..

I find other solutions for this case:

SQL Injections EXAMPLES

Comment by Tommy — October 22, 2007 @ 9:41 am

why such a name? mysql_REAL_escape_string()?

Read a PHP rant here: http://www.bitstorm.org/edwin/en/php/

Comment by why? — October 22, 2007 @ 12:07 pm

Speaking of other languages: Perl’s DBI extremely strongly discourages ever inserting user input directly into a SQL query; even if the rdbms doesn’t support prepared statements, DBI will fake them for you. SQL injection is thus impossible. I don’t know why you PHP guys haven’t been using mysqli; this is an absolutely trivial problem that was solved years ago, yet I keep seeing more and more convoluted hacks to emulate functionality built into both PHP and MySQL.

Of course, Perl, Ruby, and Python are all big on ORMs lately, and there are at least a couple solid ones for each language.

Comment by Eevee — October 22, 2007 @ 12:39 pm

Useful functions…

function mstr($value) {
if (get_magic_quotes_gpc()) $value = stripslashes($value);
return mysql_real_escape_string($value);
}

function num($val) {
if (!is_numeric($val)) return 0;
return $val;
}

Comment by Boiss — October 22, 2007 @ 4:57 pm

I believe web developers should know about these security threats before programming websites. Once you know about the threats and how to stop them, you should look into a framework such as CodeIgniter which has most of these basic measures built into the core, so you can worry about more important things such as finishing your website!

Comment by Daniel Errante — October 24, 2007 @ 10:06 am

In java as well, the syntaxes are pretty much similar :
PreparedStatement s;
s = conn.prepareStatement( “INSERT INTO” + ” ”
+ calendarTable + ” ”
+ “(serverCalendarID,
serverAPI,
calendarName)”

+ ” VALUES”
+ “(?, ?, ?)” );
s.setString (1, newCalendar.serverCalendarID);
s.setString (2, newCalendar.serverAPI);
s.setString (3, newCalendar.calendarName);
s.executeUpdate ();

Comment by rohitj — October 24, 2007 @ 2:29 pm

[…] - Protecting Your PHP/MySQL Applications from SQL Injection From the Digg description: SQL injection is a serious concern for webmasters, as an experienced […]

Pingback by Devlounge | Friday Focus #51 — October 26, 2007 @ 12:01 am

[…] Protecting Your PHP/MySQL Queries from SQL Injection […]

Pingback by BlogBuzz October 27, 2007 » Webmaster-Source — October 27, 2007 @ 6:03 am

[…] read more | digg story […]

Pingback by My Hobby is Programming » Blog Archive » Protecting Your PHP/MySQL Applications from SQL Injection — October 27, 2007 @ 6:30 am

1. Check data validity. e.g. if it’s numeric or it matches some regular expression.
2. Strip off special characters
3. Use database security settings (separate read only from others even for the same user)

The other option is to use databases that allow you to have stored procedures and ovoid SQL injection.

Comment by Jokes — November 5, 2007 @ 7:52 pm

If you liked this article then I’d highly recommend taking it further with this article: http://www.talkphp.com/showthread.php?t=1062

October 21, 2007

Protecting Your PHP/MySQL Queries from SQL Injection

31 Comments »

Leave a comment