Is your database safe from SQL injection attacks?
To begin with, let's define a SQL injection attack. What is SQL injection? SQL injection attack occurs when an attacker exploits an user input mechanism on a Website to change the outcome of a MySQL query.example #1
Let's say we programmed a login form that prompts the user to enter an username and a password. The input is processed using the following code:$sql = mysql_query("SELECT * FROM users WHERE username='".$_POST['username']."' AND password='".$_POST['password']."'");
We didn't escaped $_POST['username'] nor $_POST['password']. An attacker could exploit this vulnerability by simply entering the following in the password field:' OR username LIKE '%The query that will executed on the database would be...
// This command will select all records in the user table and grant login access.
// In other cases, an attacker could even delete the whole table!
SELECT * FROM users WHERE username='' AND password = '' OR username LIKE '%'
echo $query;
That's why you should always escape data from external sources before running the query. By "external source" I mean $_POST or $_GET data gathered by a form or $_GET data gathered by a hyperlink. Again: escape all data from user input before running the query. Write this rule on a bright yellow sticky note and place it on your monitor. Set a daily remainder on your cellphone calendar every morning. Open up Photoshop and design a desktop wallpaper that reads ESCAPE ALL DATA FROM USER INPUT. Write it on your forehead. A L W A Y S escape data from external sources.
Now, there is the right way and the wrong way to escape data. Well, at least on my humble opinion. Some articles online will teach you how to escape data using magic quotes. I do not recommend using magic quotes to escape data. On the contrary, I encourage you to disable magic quotes.
What is magic quotes?
Magic quotes is a process that escapes incoming data to a PHP script. For example, when it is set to On then single quotes, double quotes, backslashes and NULL characters are escaped with a backslash automatically. It affects GET, POST and COOKIE data.
Turn off magic quotes, NOW!
If the magic quotes setting is set to On, you might end up inserting extra slashes to your database. For example, let's say you coded a form that prompts for 3 required fields: name, email and a brief comment. So you code the script in a way that it checks the user doesn't leave any of those fields empty. If the user fails to submit an email address, your script will display an error prompting the user to enter an email address and the contents of the name and comment fields will re-display what the user has entered so far.
The re-displayed text might have slashes the magic quotes setting added.
What's wrong about this approach? You may end up with extra slashes and now you would have to strip them out.
The MySQL injection attack solution
My recommendation is switch off magic quotes and escape characters using mysql_real_escape_string.
The mysql_real_escape_string function escapes special characters in a string for use in a SQL statement. This function returns the escaped string on success, or FALSE on failure.
The following characters are affected:
- \x00
- \n
- \r
- \
- '
- "
- \x1a
Whenever you are going to process input data from a form, use the mysql_real_escape function. It's as simple as the following...
$name = mysql_real_escape_string($_POST['name']);
$email = mysql_real_escape_string($_POST['email']);
$comments = mysql_real_escape_string($_POST['comments']);
I recommend the use of this function over other methods of escaping quotes and other special characters because mysql_real_escape_string calls the MySQL library, it's a MySQL developer's solution rather than a solution provided by the PHP developers.
