Fixing SQL Injection in PHP and MySQL

Note: This post is part of our series on “How to Fix SQL Injection Vulnerabilities“. The series contains examples on how to fix SQL Injection Vulnerabilities in various programming languages.

An SQL Injection attack is a code injection attack when input from an attacker reaches one of your databases without any filteration or validation. As a result, a malicious user can execute Read / Write / Delete / Update query in your database. In addition to this he can also run system level commands. PHP for example has simple ways in preventing these attacks by filtering user inputs. Some of them are listed below:

Escaping
It’s always a good practice to filter out the user input i.e. escaping the user input before passing it to the database. The mysql_real_escape_string() function escapes special characters in a string for use in an SQL statement.

The following characters are affected:

x00, n, r, , ', ", x1a

This function returns the escaped string on success, or FALSE on failure.

// User input is escaped using mysql_real_escape_string()
   $user = mysql_real_escape_string($user);
   $pwd = mysql_real_escape_string($password);

Note: This function will only escape string values where expected input and output are strings; not integers, date or other types.

Parameterized Query
A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. The placeholders are typically type-specific (for example, int for integer data and text for strings) which allows the database to interpret the data strictly. For instance, a text placeholder is always interpreted as a literal, avoiding exploits such as the query stacking SQL injection. A mismatch between a placeholder’s type and its incoming datum causes execution errors, adding further validation to the query.

// Basic query to be used, with ? representing the input data
   $csquery = "SELECT `fname` FROM students WHERE (`sname` LIKE ?);

// Parse the input data through the function to escape any malicious data
   $sname = mysqli_real_escape_string ( $dblink, $_GET[‘sname’] );

// Initialize transaction with database
   $cs = mysqli_stmt_init ( $dblink );

// Send base query to database
   mysqli_stmt_prepare ( $cs, $csquery ) or die ( "Error: Please contact the administrator" );

// Send parameter type and value separately to the database
   mysqli_stmt_bind_param ( $cs, "s", $sname);

// Execute Query
   mysqli_stmt_execute ( $cs );

In the above example the query is initialized with “?” (placeholder for sname) representing user input. The input is then escaped using mysql_real_escape_string(). Next, database connection is initialized and the query is passed to the database without the value. The value and its type is type is then passed to the database. The query is then executed and values are displayed using the while loop

Note: You can pass multiple parameters to the database at once.

Data Validation
Using specific data type is another way how you can filter user input. For example if user input is age, amount etc; convert the data into numeric format. Other validation could be specific to the values expected by the database like date, email etc. This would not prevent SQL injection but it will make the attacker’s job a bit harder.

$age = int($_POST['age']);
$query = "SELECT * FROM students WHERE age = $age";

In the above example the value for age is validated using an int function. This would prevent user from passing any string character to the database.

Stored Procedure
Stored procedures are set of SQL commands that are stored in the database data server. After the storing of the commands is done, the tasks can be performed or executed continuously, without being repeatedly sent to the server. This also helps in decreasing the traffic in the networks and also reduces the CPU load.
Why use a stored procedure?

• Develop the functionality once and all the applications can call the same commands.
• Network Traffic reduced to a greater extent.
• Centralization of all commands made possible, which is helpful for various applications that repeatedly call the same set of complicated commands.
• Runs on any kind of environment.

DELIMITER $
// Check if stored procedure exists, if yes delete it and create new one.
   DROP PROCEDURE IF EXISTS `UName`.`get_user`$
   CREATE PROCEDURE  `Uname`.`get_user`
   (

// Pass the value for userId to the stored procedure
   IN userId INT,

// Return the value after the stored procedure has executed
   OUT firstName VARCHAR(100),
   OUT lastName VARCHAR(100)
   )
   BEGIN
   SELECT first_name, last_name
   INTO firstName, lastName
   FROM users
   WHERE users_id = userId;
   END $
   DELIMITER ;