HELLO
I AM
JOHN

I've been breaking making websites for over 15 years.

Preventing SQL Injection with PHP

Posted on July 12, 2007 at 7:49 pm

RSS 2.0

sql injectionSQL Injection. We’ve all heard the term. But what exactly is it? Sounds dirty and vile… or maybe something you get at the doctor’s office. A new type of plastic surgery maybe? Well, let’s find out, shall we?

From Wikiedpia.com:

SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.


What does this all mean? Basically it means someone takes your queries, inserts additional commands, causes your code to do things you didn’t intend it to do – potentially damaging or changing your data, gaining administrative priveleges, or worse.

Many PHP developers don’t think about this, assuming that PHP and mySQL take care of everything to make your code bullet proof. This is unfortunately not the case – or is it? PHP has built-in functions which allow you to sanitize and secure your code and queries, but they’re not automatic – you have to call them manually.

So what does a SQL Injection look like? How does one happen?

Let’s take the following example:

You have a login-form that accepts a username and password. When submitted, the form values are passed through the $_POST variable and received and processed.

login_form.php

An attack happens when a user enters in values that you don’t plan for and through that, tricks SQL into doing all the aforementions evil evil things.

Take the following process_form.php form example:

BAD process_form.php

If the would-be attacker entered admin and iamgod in the previously mentioned form, the sql statement would look like this to the SQL server:

But what if the would-be attacker entered in something different… what if for the password field, they entered the following:

iamgod’ OR 1=’1

The SQL statement would then look like this:

The 1=1 would always be true, therefore side-stepping around the username and password check. Using methods similar to this, an attacker could manipulate your code in a variety of malicious ways by injecting whatever they wanted.

So the million dollar question…

HOW DO I PREVENT A SQL INJECTION

There are a variety of ways to prevent SQL Injections. The most basic one is the one I will discuss. For further reading on the subject, please see the links at the bottom of this article. Sanitizing your $_POST variables is very easy, and is a good beginning towards developing bullet-proof code. In PHP, I recommend at the very least that you do the following:

Going along with our previous example, here’s process_form.php rewritten:

BETTER process_form.php

Did you see it? mysql_real_escape_string() function escapes any characters that could potentially change your query.

From www.php.net:

[mysql_real_escape_string…] escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

mysql_real_escape_string() calls MySQL’s library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ‘, ” and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

And that’s how to protect yourself from basic SQL injections. There are other preventative measure you can take. As a rule of thumb, I always try to validate my form data before it even gets to the sql statement. Am I expecting a string or an integer? What did I receive? What do I want to do with this?

But basically, to be safe, at the least remember the following rules:

  • Never trust the user!
  • Validate your data!
  • Always escape your characters!
  • Never trust the user!

If you can do that, then you’re off to a good start. Good luck, keep it clean kiddies.

Further reading on SQL Injections:

Share

Written by

John is a web-developer by day, dashing, handsome, and death-defying super-hero by night. Based out of Silver Spring, MD, he does his best to make things do things other than the things they were supposed to on a daily basis, in the process, making the world a better place.

There are 17 comments.

Comment by gsweet

best explanation of sql injections i’ve seen!
A+ !

JUL17 2007 06:54:00
Comment by digifuzz

Thanks :)

JUL17 2007 09:47:00
Comment by Jedite

This was very informative. Good info about this on slashdot, too

JUL19 2007 09:49:00

This is a good article. Stumbled!

JUL31 2007 18:39:00
Comment by Jo

Thanks! Very helpful!

AUG06 2007 03:40:00
Comment by JHB

Best explanation ever, you sure know what you’re talking about

AUG06 2007 13:47:00
Comment by voltHeir

why reinvent the wheel? use PDO! then you are truly preventing SQL injection, not simply PHP’s native MySQL compatibility injection.

There are ways around mysql_escape…

AUG06 2007 14:19:00
Comment by wookienz

great stuff…cheers.

APR03 2008 05:01:00
Comment by Dharam

A great explanation ! ! ! !

FEB25 2009 00:13:00
Comment by Javatechie

Thnaks.. very helpful!

MAY14 2009 23:49:00
Comment by Sarah

Very easy to understand.. thanks for the article.
Regards
Sarah

MAY22 2009 23:37:00
Comment by Mukund

Thanks
I learned lot from this article

JUN05 2009 02:05:00
Comment by shailesh

Thanks…..

AUG18 2010 23:25:00
Comment by vicky patel

‘ or 1′
1=1–
‘ or ”=’
1’or’1’=’1
0’or’0’=’0
admin’–
‘ or 0=0 —
” or 0=0 —
or 0=0 —
‘ or 0=0 #
” or 0=0 #
or 0=0 #
‘ or ‘x’=’x
” or “x”=”x
‘) or (‘x’=’x
‘ or 1=1–
” or 1=1–
Here some sql injection code for you. you can test ur site with these all for sql injection..

or 1=1–
‘ or a=a–
“or “a”=”a
‘)or (‘a’=’a
“) or (“a”=”a
hi” or “a”=”a
hi” or 1=1–
hi’ or 1=1–
hi’ or ‘a’=’a
hi’) or (‘a’=’a
hi”) or (“a”=”a

Happy Coding :)

MAR22 2011 00:40:00
Comment by Eyal

This method wont block any sql injection becouse it can recive a query like this :
“DROP TABLE” and it wont be filtered by mysql_real_escape_string function.

DEC05 2012 14:02:00
Comment by karim

thnx

MAY16 2013 03:56:00
Comment by marvin

nice tutorial it helps a lot..tnx to you,

JUL07 2014 21:23:00

Trackbacks

  1. Mimic AJAX Form Posts with PHP, Javascript, and Cookies » digifuzz.net
  2. How can I prevent SQL injection in PHP? | Route For Solution
  3. How to: How can I prevent SQL-injection in PHP? | SevenNet

Allowed tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

By submitting a comment you grant digifuzz.net a perpetual license to reproduce your words and name/web site in attribution. Inappropriate and irrelevant comments will be removed at an admin’s discretion. Your email is used for verification purposes only, it will never be shared.