:::: MENU ::::
  • Feb 20 / 2012
  • 0

SQL injection attack and preventing SQL injection

Database Security

Nowadays, databases are cardinal components of any web based application by enabling websites to provide varying dynamic content. Since very sensitive or secret information can be stored in a database, you should strongly consider protecting your databases.

To retrieve or to store any information you need to connect to the database, send a legitimate query, fetch the result, and close the connection. Nowadays, the commonly used query language in this interaction is the Structured Query Language (SQL).

The more places you take action to increase the protection of your database, the less probability of an attacker succeeding in exposing or abusing any stored information. Good design of the database schema and the application deals with your greatest fears.

SQL Injection

Many web developers are unaware of how SQL queries can be tampered with, and assume that an SQL query is a trusted command. It means that SQL queries are able to circumvent access controls, thereby bypassing standard authentication and authorization checks, and sometimes SQL queries even may allow access to host operating system level commands.

Direct SQL Command Injection is a technique where an attacker creates or alters existing SQL commands to expose hidden data, or to override valuable ones, or even to execute dangerous system level commands on the database host. This is accomplished by the application taking user input and combining it with static parameters to build an SQL query.

Owing to the lack of input validation and connecting to the database on behalf of a superuser or the one who can create users, the attacker may create a superuser in your database.

SQL Injection  Example

SQL code is injected into the SQL query. Allows attacker to do almost anything the database user is permitted. Resulting SQL statement always returns all the data from the ‘USERS’ table. Further attack possibilities: Insert data, Delete data, Read data, Denial of service….


An example of SQL Injection Attack

// We didn't check $_POST['password'], it could be anything the user wanted!
// For example:
$_POST['username'] = 'david';
$_POST['password'] = "' OR ''='";

// Query database to check if there are any matching users
$query = "SELECT * FROM users WHERE user='{$_POST['username']}' AND 

// This means the query sent to MySQL would be:
echo $query;

The query sent to MySQL:

SELECT * FROM users WHERE user='david' AND password='' OR ''=''

This would allow anyone to log in without a valid password.

SQL injection prevention methods or SQL injection Mitigation

  1. Use prepared statements with bound variables when supported by the database. They are provided by PDO, by MySQLi and by other libraries.
  2. If the database layer doesn’t support binding variables then quote each non numeric user supplied value that is passed to the database with the database-specific string escape function like, mysql_real_escape_string(), addslashes(), magic_quotes_gpc, get_magic_quotes_gpc(), stripslashes(), htmlentities, htmlspecialchars.
  3. Never connect to the database as a superuser or as the database owner. Use always customized users with very limited privileges.
  4. PHP has a wide range of input validating functions. So check if the given input has the expected data type like variable, character (e.g. is_numeric(), ctype_digit() respectively).
  5. Do not print out any database specific information, especially about the schema, by fair means ( Error Reporting and Error Handling and Logging Functions. ) or foul.


Use prepared statements when supported by the database.

Many of the more mature databases support the concept of prepared statements. Prepared statements are a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters. Prepared statements offer two major benefits:

  • The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query.
  • The parameters to prepared statements don’t need to be quoted; the driver automatically handles this.

The MySQL database supports prepared statements. A prepared statement or a parametrized statement is used to execute the same statement repeatedly with high efficiency.

Basic workflow

The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is send to the database server. The server performs a syntax check and initializes server internal resources for later use.

The MySQL server supports using anonymous, positional placeholder with ?.

Repeated inserts using prepared statements

This example performs an INSERT query by substituting a name and a value for the positional  ?  placeholders.

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insert one row
$name = 'one';
$value = 1;

// insert another row with different values
$name = 'two';
$value = 2;

Use database-specific escaping functions

There are mainly two type of database-specific escaping functions, functions for storing data submitted from a Form for use in an SQL query and displaying data from a Database.

  • Storing data submitted from a Form
  • Retrieving data for display in a browser

Storing data submitted from a Form

Storing data in a database requires escaping certain characters that could either be interpreted by the database engine as a command or that might generate an error.



This function escapes special characters in a string for use in an SQL statement. If this function is not used to escape data, the query is vulnerable to SQL Injection Attacks.

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 .

Simple mysql_real_escape_string() example

// Assume user entered username is david and password is ‘ OR ‘’=’ 
$user = $_POST['username']
$password = $_POST['password']

// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    OR die(mysql_error());

// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",

Returns a string with backslashes before characters that need to be quoted in database queries etc.  These characters are single quote (‘), double quote (“), backslash (\) and NUL (the NULL byte).

An example use of addslashes() is when you’re entering data into a database. For example, to insert the name O’neil into a database, you will need to escape it. It’s highly recommended to use DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_string() for PostgreSQL), but if the DBMS you’re using doesn’t have an escape function and the DBMS uses \ to escape special chars, you can use this function. This would only be to get the data into the database, the extra \ will not be inserted. Having the PHP directive magic_quotes_sybase set to on will mean ‘ is instead escaped with another ‘.

$str = "Is your name Martin O'neil?";

// Outputs: Is your name Martin O\'neil?
echo addslashes($str);

Sets the magic_quotes state for GPC (Get/Post/Cookie) operations. When magic_quotes are on, all ‘ (single-quote), ” (double quote), \ (backslash) and NUL’s are escaped with a backslash automatically.

The PHP directive magic_quotes_gpc was on by default before PHP 5.4, and it essentially ran addslashes() on all GET, POST, and COOKIE data. Do not use addslashes() on strings that have already been escaped with magic_quotes_gpc as you’ll then do double escaping. The function get_magic_quotes_gpc() may come in handy for checking this.

If magic_quotes_gpc is enabled, first apply stripslashes() to the data. Using this function on data which has already been escaped will escape the data twice.

Retrieving data for display in a browser


Once you have retrieved data from a database, you will want to display it. But in the same way that the data we were inserting into the database may contain characters which ‘break’ our SQL, there may be characters which will ‘break’ our output, so we need to ‘escape’ (also called ‘converting’ or ‘encoding’) our data for output.


The most common use of PHP is to display the data in an HTML page. However there are many characters which will wreak havoc with HTML (called HTML ‘entities’ because they have special meaning in HTML). Htmlentities will convert these characters to their html-safe equivalents(Convert all applicable characters to HTML entities ).


Convert special characters to HTML entities. Certain characters have special significance in HTML, and should be represented by HTML entities if they are to preserve their meanings. This function returns a string with these conversions made. If you require all input substrings that have associated named entities to be translated, use htmlentities() instead.


Leave a comment

Before hitting COMMENT button, please fill * marked fields and the correct reCAPTCHA response.