About the Author

Chris Shiflett

Hi, I’m Chris: web craftsman, community leader, husband, father, and partner at Fictive Kin.

SQL Injection

  • Published in PHP Architect on 15 Apr 2004
  • Last Updated 15 Apr 2004

Thanks to Alyona Lompar, this article is also available in Ukrainian.

Welcome to another edition of Security Corner. This month's topic is SQL injection, an attack vector that frequents the minds of PHP developers, but for which there is a shortage of good documentation.

Most web applications interact with a database, and the data stored therein frequently originates from remote sources. Thus, when creating an SQL statement, you often use input in its construction. A typical SQL injection attack exploits this scenario by attempting to send fragments of valid SQL queries as unexpected values of GET and POST data. This is why an SQL injection vulnerability is often the fault of poor filtering and escaping, and this fact cannot be stressed enough.

This article explains SQL injection by looking at a few example attacks and then introducing some simple and effective safeguards. By applying best practices, you can practically eliminate SQL injection from your list of security concerns.

SQL Injection

For a moment, place yourself in the role of an attacker. Your goal is initially simple. You want to get any unexpected SQL statement executed by the database. You're only looking to get something to work, because that will reveal the fact that the application has a potential vulnerability. You have as many chances as you want, and you have a lot of information to work with. For example, consider the simple authentication form shown in Figure 1.

Figure 1:

In order to get more information about this form, you view the source:

<form action="/login.php" method="POST">
<p>Username: <input type="text" name="username" /></p>
<p>Password: <input type="text" name="password" /></p>
<p><input type="submit" value="Log In" /></p>

You can already make a very educated guess about the type of SQL statement that this application might use to verify the access credentials. It will most likely be a SELECT statement. You can also make a guess about the naming convention used in the database table, because it probably matches the simple names used in the HTML form. (It's also possible that you can cause an error that reveals this information to you.) Because this form is for authentication, there is probably WHERE clause that uses $_POST['username'] and $_POST['password'].

From all of this, you might predict the following:

$sql = "SELECT count(*)
        FROM   users
        WHERE  username = '{$_POST['username']}'
        AND    password = '...'";

Assuming this guess is correct, what can you do to manipulate this query? Imagine sending the following username:

chris' /*

The SQL statement becomes the following:

SELECT count(*)
FROM   users
WHERE  username = 'chris' /*'
AND    password = '...'";

In this example, /* is used to begin a multi-line comment, effectively terminating the query at that point. This has been tested successfully with MySQL. A standard comment in SQL begins with --, and it's trivial to try both.

This query suggests a successful authentication attempt as long as the chris account exists, regardless of the password. This particular attack is frequently used to steal accounts. Of course, any username can be used (admin is a popular target). Thus, by sending a malformed username, you can manage to log in without having a valid account.

Keep in mind that creativity plays a large role in most attacks. In the previous example, the attack is limited by the type of query (SELECT) and in the way the username and password are used. In other words, as an attacker, you are somewhat bound, and your attacks must try to exploit the situation within these bounds. Other types of queries present new opportunities, and the best practices mentioned in this article apply to all SQL injection attacks.

WHERE Hacking

The WHERE clause is used to restrict the records that a particular query matches. For a SELECT statement, it determines the records that are returned. For an UPDATE statement, it determines the records that are modified. For a DELETE statement, it determines the records that are deleted. If a user can manipulate the WHERE clause, there are many opportunities to make drastic changes - selecting, updating, and deleting arbitrary records in the database.

Imagine a SELECT statement intended to fetch all credit card numbers for the current user:

$sql = "SELECT card_num, card_name, card_expiry
        FROM   credit_cards
        WHERE  username = '{$_GET['username']}'";

In this particular case, the application might not even solicit the username but instead provide it in a link:

<a href="/account.php?username=shiflett">
Credit Card Information

If a user can have multiple cards, the application might loop through the results of a database query, displaying the card number, name on the card, and expiration date for each card.

Imagine a user who visits the following resource:


This submits the following value for the username:

shiflett' OR username = 'lerdorf

If used in the previous SQL query, $sql has the following value:

SELECT card_num, card_name, card_expiry
FROM   credit_cards
WHERE  username = 'shiflett' OR username = 'lerdorf'

Now the user sees a list of all credit cards belonging to either shiflett or lerdorf. This is a pretty major security vulnerability. Of course, a larger vulnerability exists in this particular example, because a user can arbitrarily pass any username in the URL. In addition, a username that causes the WHERE clause to match all records can potentially expose all records:

shiflett' OR username = username

Imagine if this particular username is stored in the database (using a separate SQL injection attack) as the attacker's own username. Every query that is restricted by a WHERE clause in order to only apply the user's own record can potentially apply to all records instead. This is not only extremely dangerous, but it also makes further attacks very convenient.

Input Filtering

This article assumes magic_quotes_gpc is disabled. If it is enabled, you can disable it or use the fix_magic_quotes() function to repair the input.

There are best practices that you should follow to prevent SQL injection attacks, and these offer a very high level of protection. The most important step is to filter all input (data that comes from a remote source). This includes $_GET, $_POST, $_COOKIE, etc. To help clarify this, consider the following HTML form:

<form action="/receive.php" method="POST">
<select name="color">
    <option value="red">red</option>
    <option value="green">green</option>
    <option value="blue">blue</option>
<input type="submit">

Clearly, the expected values are red, green, and blue. So, the input filtering should verify this:

$clean = array();
switch ($_POST['color']) {
    case 'red':
    case 'green':
    case 'blue':
        $clean['color'] = $_POST['color'];
        /* Error */

This code uses a separate array ($clean) to store the filtered data. It is a good idea to choose a naming convention that will help you identify potentially tainted data. In this example, $clean['color'] can be trusted to contain a valid color, because it is first initialized and then only assigned the value of $_POST['color'] if that value passes the test.

The two most important points for input filtering are:

  • Only accept valid data rather than trying to prevent invalid data.
  • Choose a naming convention that helps you distinguish tainted data from filtered data.

Escaping Output

With properly filtered input, you're already pretty well protected against malicious attacks. The only remaining step is to escape it such that the format of the input doesn't accidentally interfere with the format of the SQL statement. If you are using MySQL, this simply requires you to pass all data through mysql_real_escape_string() prior to use:

$mysql = array();
$mysql['color'] = mysql_real_escape_string($clean['color']);
$sql = "SELECT username
        FROM   users
        WHERE  favorite_color = '{$mysql['color']}'";

In this case, assuming $clean['color'] is created by the previous example, you can be sure that the color only contains alphabetic characters. (It's one of red, green, or blue.) Thus, the escaping might seem superfluous, and it is. However, it is still a good habit to always escape data. This practice will help you avoid forgetting this crucial step, and it adheres to the principle of defense in depth, which stresses the importance of redundant safeguards.

Until Next Time...

Preventing SQL injection is easy, but it is one of the most common web application vulnerabilities. Hopefully you will now always adhere to the following guidelines:

  • Filter input.
  • Escape output.

NYPHP has a helpful resource that explains SQL escaping.

I hope you can now safely protect your applications against SQL injection attacks. Until next month, be safe.

About this article

SQL Injection was last updated on 15 Apr 2004. Follow me on Twitter.


1.Chris Padfield wrote:

Remember that if you need to sanitize a variable for which you do not know what the variable will be (ie a username, as opposed to a set colour) then mysql_escape_string() on that variable will prevent any injection attacks.

Mon, 13 Dec 2004 at 17:20:03 GMT Link

2.Gordon wrote:

Further reading on the subject seems to suggest that addslashes() is preferred over mysql_escape_string(). In fact mysql_escape_string() is depreciated as of PHP 4.3.0

Tue, 01 Feb 2005 at 18:01:42 GMT Link

3.Chris Shiflett wrote:

No, addslashes() is definitely not preferred. This is the note I think you're referring to:

"Note: This function has been deprecated since PHP 4.3.0. Do not use this function. Use mysql_real_escape_string() instead."

I need to update all of my documentation to reflect this. Use mysql_real_escape_string() instead of mysql_escape_string(). Thanks for the reminder. :-)

Tue, 01 Feb 2005 at 18:11:53 GMT Link

4.Johannes Verelst wrote:

I would recommend writing a small library of functions to deal with escaping data. For instance, I use the following:

function quote_smart($value) {

if (get_magic_quotes_gpc()) {

$value = stripslashes($value);


if(version_compare(phpversion(),"4.3.0") == "-1") {

return mysql_escape_string($value);

} else {

return mysql_real_escape_string($value);



Also, you should make sure you only escape your data once. If you do this:

$clean['color'] = mysql_escape_string($clean['color']);

$sql = "... {$clean['color']} ...";

then make sure you won't copy this code at the end of the file, since it will double-escape everything.

Wed, 02 Feb 2005 at 08:29:28 GMT Link

5.Matt wrote:

I think magic_quotes is the best option for most people... magic_quotes provides an automated 'guarantee' against attackes...

If you're an advanced developer that is developing a critical PHP application in an enterprise enviroment, then the recommendations of this article may be relivant...

Mon, 07 Feb 2005 at 09:32:16 GMT Link

6.Jeff wrote:

Another thing I always do is replace characters with their URL encoded counterpart.


" = %22

' = %27

? = %3F

& = %26

Sun, 13 Feb 2005 at 04:39:35 GMT Link

7.Chris Shiflett wrote:

Matt, I think magic_quotes is one of the worst features PHP has. It has more problems than addslashes(), particularly because it inherits those same problems while adding some problems of its own (for example, it escapes input, a poor approach).

Jeff, URL encoding is an escaping method meant for data to be used in a URL. It has nothing to do with escaping data destined for a database. For the latter, you should rely on an escaping function specific to your database, such as mysql_real_escape_string() for MySQL.

Mon, 14 Mar 2005 at 20:33:54 GMT Link

8.Stan wrote:

I sometimes use base64_encode() when I have to accept a large amount of freeform data from a web form, such as a Comments field. I store the data in base64 encoded format in the database. The downside is that it can't be easily searched. The upside is that I don't have to worry about SQL injection or escaping. When I need to use the data, I use base64_decode() after retrieving it from the database.

I also use this technique sometimes when I need to pass data from one form to another without having to store the transient data on the server. I simply create a hidden field and base64_encode() the data to be passed. I still have to verify the data on the receiving end, but it simplifies the movement of data across multiple forms.

Several fields of data can passed between forms as one string, separated by a |, and then encoded as a single hidden variable. On the receiving end, I use explode("|",$_POST[data]) to retrieve my data.

Wed, 23 Mar 2005 at 17:12:08 GMT Link

9.Ed wrote:

Getting back to the URL escaping, I realize that it's not _meant_ to escape SQL, but wouldn't it work? Especially if your writing generic code for a server that may or may not have the mysql_escape_string() or mysql_real_escape_string() available? I have ran into problems using the html escape functions for this, but would a custom escaping function, like "? = '[some_tag]'" on the input then "'[some_tag]' = ?" on the output, work in the security sense? I can see that it would probably be slower running a substr() or preg_replace() than a built-in function, but if the purpose is security, then the slowdown would be acceptable if it does the job. Or am I just really missing something here?

Sun, 03 Apr 2005 at 18:07:11 GMT Link

10.Chris Shiflett wrote:

Ed wrote:

> Getting back to the URL escaping, I realize that

> it's not _meant_ to escape SQL, but wouldn't it

> work? Especially if your writing generic code for

> a server that may or may not have the

> mysql_escape_string() or

> mysql_real_escape_string() available?

That's impossible. If you don't have mysql_escape_string() or mysql_real_escape_string(), then you don't have MySQL support.

Sun, 03 Apr 2005 at 18:18:17 GMT Link

11.Ed wrote:

>> a server that may or may not have the

>> mysql_escape_string() or

>>mysql_real_escape_string() available?

>That's impossible. If you don't have mysql_escape_string() or >mysql_real_escape_string(), then you don't have MySQL support.

Fair enough. Thanks!

Sun, 03 Apr 2005 at 21:17:36 GMT Link

12.Eugene wrote:

What is the reverse of mysql_real_escape_string()?

Wed, 11 May 2005 at 10:06:42 GMT Link

13.Chris Shiflett wrote:

Eugene, you should never have to reverse that. If you do, it means you've done something wrong. :-)

The process of escaping should preserve data, so it should never be necessary to reverse it. When I'm auditing an application, things like stripslashes() alert me to design problems.

Mon, 16 May 2005 at 15:00:42 GMT Link

14.John wrote:

Chris, what's your recommendation on whether to use mysql_real_escape_string() or base64_encode() on upto 64kb freeform data fields, like user comments or articles.

Wed, 18 May 2005 at 00:24:01 GMT Link

15.Daniel Braga wrote:

Cris, sees if my class is secure: http://www.comentar.com.br/daniel/?p=5

Fri, 17 Jun 2005 at 17:14:04 GMT Link

16.Paul McClean wrote:

regarding your email validation logic, the regular expression you posted:

$email_pattern = '/^[^@s]+@([-a-z0-9]+.)+[a-z]{2,}$/i';

does not prevent users from entering punctuation into the string, example:

^_^@hotmail.com is seen as 'error free' by the regexp

ironically, in an article about SQL Injection, dd+=@hotmail.com is also seen as error free.

Hope this helps ;)

Tue, 09 Aug 2005 at 19:55:00 GMT Link

17.Paul McClean wrote:

This would be a more useful pattern:

$email_pattern = '/^[a-zA-Z0-9\._-]+\@(\[?)[a-zA-Z0-9\-\.]+'.'\.([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$/';

Tue, 09 Aug 2005 at 20:09:29 GMT Link

18.Frank van Wensveen wrote:

I'm not an experienced PHP or SQL programmer yet (working on it, though) so this is probably a real newbie question.

It seems a bit of a pain to take existing (non-secure) code and go trough it line by line and apply mysql_real_escape_string() to each SQL query separately.

I'd rather do this once, in the code layer I put between my PHP code and the underlying SQL database.

For example, I have call a function db_query() that passes on the query to mysql_query(). When I decided to to it that way the idea was that I could not only insert debugging code there (being in the learn-as-you-mess-up stage as I am) but also that one day I might have to apply existing code to another SQL database instead of MySQL.

Question: is it possible to just apply mysql_real_escape_string() to the entire query string in this interface layer, regardless of what that query string is, and thereby take care of all my pains, woes and worries in one fells swoop? (That seems too good to be true so it probably is... <g>)

Any response would be appreciated!

Wed, 17 Aug 2005 at 14:55:21 GMT Link

19.Mark wrote:

helpful php site for learning to use regex's:


Thu, 25 Aug 2005 at 04:36:50 GMT Link

20.Jennyfer Tepps wrote:

I like yout articles very much, Chris.

Please, write more!

Sun, 11 Sep 2005 at 12:11:45 GMT Link

21.Adrian wrote:

In which way should mysql_real_escape_string() be superior to addslashes() other than some cosmetic considerations?

The MySQL manual says concerning the function called by php's mysql_real_escape_string():

"Strictly speaking, MySQL requires only that backslash and the quote character used to quote the string in the query be escaped. This function quotes the other characters to make them easier to read in log files."

So, concerning security, addslashes() should be indentical to mysql_real_escape_string()...

Sat, 22 Oct 2005 at 18:59:30 GMT Link

22.Richard wrote:

After thinking about this I've decided to try to never include client-supplied info into a SLQ statement. Thus I've converted at least one instance of a "forgot my password" to the following...

$q="SELECT * FROM tbl";


while ($stuff=mysql_fetch_array($sql))


if ($_POST['email'] == $stuff['email'])


Do the appropriate stuff....



I might not always be able to accomplish my mission with this approach, but at least my databases should be safe from this peril.... (OK, now everyone tell me why this doesn't work or help plug those risks, or creates new risks!)

Great article/column!

Thu, 10 Nov 2005 at 23:11:07 GMT Link

23.Jullian wrote:

Here is a great article about sql injection with a working solution.


Tue, 15 Nov 2005 at 14:38:00 GMT Link

24.Vincent wrote:

I'm not a security expert but after some URL attacks I have implemented this simple thing. Until today it works for me. I will be glad to read some thoughts to improve it.

I put it before the connection with DB. I have this inside one only script outside the public space. Then it is requested by means an include in every php script. In this way, this security works in the whole site.



$cadena = explode("?", $req);

$mi_url = $cadena[0];

$resto = $cadena[1];

// suspicions chains at will. Just be careful with

// the names of your variables passing by you URLs

$inyecc='/script|http|<|>|%3c|%3e|SELECT|UNION|UPDATE|AND|exe|exec|INSERT|tmp/i'; ...etc

// detecting

if (preg_match($inyecc, $resto)) {

// kill execution

echo 'illegal url';



// Here starts the DB connection

$connection=mysql_connect(... etc.


Mon, 28 Nov 2005 at 21:04:55 GMT Link

25.vedeney wrote:

Vincent, but your way of filtering is unuseful if someone will desire to use COOKIES || $_POST for sql-injection

Thu, 29 Dec 2005 at 22:40:42 GMT Link

26.Neil Norton wrote:

Agree to Vincent. Very nice solution! Just killing illegal part of url will be quite enough. However doublechecking of all variables to be used in query of course also should be done. It is just a politeness of programming :)

Tue, 10 Jan 2006 at 07:15:28 GMT Link

27.vvv wrote:

Why wouldn't just enabling magic quotes work?

Sun, 05 Feb 2006 at 21:48:10 GMT Link

28.xpath wrote:

blah blah blah...what is with everyone contributing to the same thing. do you people like reinventing the wheel? i'd write more but i'm going to write another sql injection white paper.

Tue, 07 Feb 2006 at 18:38:53 GMT Link

29.Frank wrote:

vvv, magic quotes will work in the beginning, but may lead into trouble later, as you might have other data sources than user input. For example, if you read text from one table and insert it into another table.

Fri, 24 Feb 2006 at 12:19:11 GMT Link

30.Binary wrote:

Well i just love sql injections, but the problum is getting it to work with a site when u have find a flaw in the software. take the torrent sites for example all the users are in the sql now they are under the table users and then your user id is the id of your account and if u change your stats to 6 that will give u sysop on the site 5 admin 4 mod 3 VIP 2 power user and 1 user 0 banned

but most user info is in the sql and most people think it is secure when it is clearly not. sql is your friend be nice to it and it will be nice back

Tue, 16 May 2006 at 16:05:59 GMT Link

31.pociwasiat wrote:

can i use this script??

function bersih2($a)


$arr = array("'",";","--","/*","*/");

$user = str_replace($arr,"",stripslashes(strip_tags(urldecode($a))));

return $user;


Wed, 26 Jul 2006 at 21:17:18 GMT Link

32.AlBug wrote:

K what one should do if it's using mysql_real_escape_string and then serialize the array and then store in MySQL

then you have a problem

what are the suggestions?


Fri, 11 Aug 2006 at 14:49:13 GMT Link

33.Chris Shiflett wrote:


I can't tell exactly what you're asking, but it sounds like you need to perform those steps in the opposite order. The escaping is just meant to preserve your data in the context of the SQL query. If what you're storing is a serialized string, do the serializing before the escaping. Then, when you pull that data out, you just need to unserialize it.

Hope that helps.

Fri, 11 Aug 2006 at 14:54:26 GMT Link

34.AlBug wrote:

Hi Chris,

I fixed my issue long before I post here. But it was a big problem for me. Because:

1) first thing I do when I'm receiving data through POST or GET I clean them with mysql_real_escape_string

2) use them in my code

3) then because I have a load balancing server I have to store cookie data into the database so I'm using serialize after each page is processed. So there is no way to put mysql_real_escape_string after serialize...

my solution was to encode \ and ' and then to decode them in my DB class for each query


Mon, 14 Aug 2006 at 17:49:58 GMT Link

35.Vince wrote:

hi AlBug

I used your solution too. But it doesnt work in one instance. Let say you have a form with values you need to submit to the database.

1. Enter form values

2. Clean all form values

3. Validate

4. Insert into database

If at point 3, your values fail validation and you need to echo them back to the screen.. You will end up with slashes where you don't want slashes.

Thu, 05 Oct 2006 at 08:06:15 GMT Link

36.bheisey wrote:

Chris, do you have a suggestion for a solution if mysql_real_escape_string is not an option? The security environment I work in has removed all mysql functions from our installation and I am forced to use Oracle & the oci functions.

Sun, 17 Dec 2006 at 16:38:55 GMT Link

37.Chris Shiflett wrote:

The mysql_real_escape_string() function helps you escape data for MySQL, not Oracle.

If you're using Oracle, your best bet is to use prepared statements. This feature is available in the OCI8 extension as well as PDO.

Mon, 18 Dec 2006 at 04:32:30 GMT Link

38.JD wrote:

Great article.

Q: I have been learning PHP, and was using stripslashes to echo back invalid entries that had slashes added... If I use mysql_real_escape_string() to clean up the data entered, am I right in thinking I don't need to alter the data any further?

Form data > mysql_real... > data is cleaned up > can be entered or echoed without further processing.



Mon, 21 May 2007 at 19:39:43 GMT Link

39.woodydrn wrote:

mysql_real_escape_string wont help if $_POST['username'] is '; delete from users;-- etc ... restrict the user from deleting or check the input for ; or -- or any sql commands.

Mon, 09 Jul 2007 at 19:34:25 GMT Link

40.Michael Peters wrote:

What about SQL bind parameters? Perl (or Python, etc) programmers don't need to worry about filtering their input data (or which of the multiple filter functions they should call on that data) if it's simply going into the database. Why is using SQL bind parameters so hard?

Tue, 04 Sep 2007 at 20:59:54 GMT Link

41.Roman wrote:

woodydrn, mysql_real_escape_string will help in the case of the input you gave too. The string will simply be a literal string properly quoted.

Sat, 18 Sep 2010 at 07:29:45 GMT Link

42.Ottoman wrote:

Hi Chris,

Could i ask a few questions?

1. If a form is using select, user can't enter anything but to select one from those display in fornt of him. So does it really need to be filtered? and then espaced?

2. If all inputs and outputs are filtered/escaped, and everything is valid. Can a hacker steal these valid data and hence gaining access to a database as if he was a valid user? And how could I prevent that?

3. What if someone is able to gain access of your database directly? For example if I have a database protected by my password with given previlige, if someone gets this password, everthing will be accessable to him. Is there a way to prevent this?

4. And if someone is able to alter my source code, and run it with what ever they want, then my entire db is exposed. Is there a way of preventing that?

5. Is there a way to make a whole website secure? So that no one can modify my source codes, touch my db as if he was me.

Many thanks

Sat, 20 Nov 2010 at 04:17:36 GMT Link

Hello! What’s your name?

Want to comment? Please connect with Twitter to join the discussion.