Fixing SQL Injection in PHP and MS SQL

Posted on by

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:

Parameterized Query

$qualification = $_POST[‘quatification’];
$sex = $_POST[‘sex’];

$sql = "SELECT Name, City, Phone FROM Students WHERE Qualification = ? AND Sex = ?; 

$params = array($qualification, $sex);
$result = sqlsrv_query($conn, $sql, $params);

Here sqlsrv_query function accepts three parameters: $conn, $sql, $params (optional):

  • $conn – Connection to the SQL Server Database
  • $sql – The SQL query to fetch the data. Question marks (?) are used as placeholders for parameters.
  • $params – Array values that correspond to the placeholders (?) in the SQL query.

Stored Procedure

In the example below a stored procedure is created and the output is binded together with the variable using mssql_bind command.

// Create a new stored prodecure
$stmt = mssql_init('NewUserRecord');

// Bind the field names
mssql_bind($stmt, '@username',  'sameer',  SQLVARCHAR,  false,  false,  60);
mssql_bind($stmt, '@name',      'Sameer',  SQLVARCHAR,  false,  false,  60);
mssql_bind($stmt, '@age',       25,       SQLINT1,     false,  false,   3);

// Execute
mssql_execute($stmt);

About Yash