packetcode logo
Preventing SQL Injection in PHP

Preventing SQL Injection in PHP

14038 views 2 years ago Tutorials PHP

SQL injection is a code injection technique, to attack web applications with malicious SQL statements that are inserted through form fields and they usually pull out the important database information. This is a very critical security breach, it can destroy the database completely, so there is an at most need to secure the apps with proper code standards.

How is it done?

There are a couple of ways a hacker can penetrate to the web apps.

First Hack:

The first is 1=1 logic, to understand this let us take a web app which executes the following statement at the back end.


$uid = $_REQUEST[‘uid’];
SELECT * FROM Users WHERE uid = “$uid”; 

Here the code is receiving the userid from the form field and it would extract the user information. Ideally the user has to enter the user id and he would get his information but if the user is smart enough then he might give an entry like this ‘120 or 1=1’. This entry would be executed as below


SELECT * FROM Users WHERE uid = 120 or 1=1;

The server understands it as, extract the user infromation if first or second condition is true. Here 1=1 is always true therefore it would extract all the user details. With a simple statement the hacker would be able to access all the user records and its a potential threat.

Second Hack:

The second way is based on equality(=) symbol. The hacker might simply send " or ""=" as a username or password and it executes as follows


SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""
	

The statement is always valid and it will return all rows from the table Users.

Third Hack :

Lastly, it could be batch statement execution, since most databases support batch statement. A smart user can run a secondary sql statement by using a semicolon separation from the user input as follows.


SELECT * FROM Users; DROP TABLE User_Feeds

This will return the user table information and deletes the user feeds table permanently.This is really dangerous, it can completely collapse the database system.

How to prevent it?

From the above examples we can easily come to a conculsion that its very risky to trust the user. We must and should have proper validation techinque to prevent sql injections. So below are few possibles solutions

#1 Using escape string

Developers usually use php function mysql_real_escape_string to filter input data. As of PHP 5.5.0 mysql_real_escape_string and the mysql extension are deprecated. So we shall use mysqli extension and mysqli::escape_string function instead. Where the unnecessary extra characters are removed and then passed to the execution.


// procedural method of calling
$uid= mysqli_real_escape_string($uid);

// Object method of calling
$uid= $mysqli->escape_string($uid);


<?php
	
	//  Full Sample code
	//	Creating connection and checkng for errors
	//	Error Statement
	//	Escaping the string and execution
	
	
	/* create connection */
	$mysqli = new mysqli("localhost", "username", "password", "database");

	/* check connection */
	if (mysqli_connect_errno()) {
		printf("Connect failed: %s\n", mysqli_connect_error());
		exit();
	}


	$city = "'s hyderabad";

	/* this query will fail, because we didn't escape $city */
	if (!$mysqli->query("INSERT into city (Name) VALUES ('$city')")) {
		printf("Error: %s\n", $mysqli->sqlstate);
	}

	$city = $mysqli->escape_string($city);

	/* this query with escaped $city will work */
	if ($mysqli->query("INSERT into myCity (Name) VALUES ('$city')")) {
		printf("%d Row inserted.\n", $mysqli->affected_rows);
	}

	$mysqli->close();
?>

#2 Using prepared statements

When prepared statements and parameterized queries are used, these SQL statements that are sent to and parsed by the database server separately from any parameters. In this way it is impossible for an attacker to inject malicious SQL.



<?php
	// Create a Connection 
	$mysqli = new mysqli("server", "username", "password", "db_name"); 
	$uname = $_POST["username"];
	//check that $stmt creation succeeded 
	// "s" means the database expects a string 
	$stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)"); 
	$stmt->bind_param("s", $uname);
	$stmt->execute();
	$stmt->close();
	$mysqli->close();
?>

When the sql statement is passed through prepare its compiled by database server and by specifying parameters we are telling database engine to filter the parameters. Later the execute statement is called to combine the parameters with the statement.It's important to note that the parameters are combined with compiled statement not sql string.

Another benefit with using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.

#3 Using pdo prepared statements

As of PHP 5.1, there's a better way. its using PDO ie. PHP Data Objects. it is a database access layer providing a uniform method of access to multiple databases.it provides methods for prepared statements and working with objects that will make you far more productive!



	$stmt = $pdo->prepare('SELECT * FROM USERS WHERE name = :name'); 
	$stmt->execute(array('name' => $name)); 
	foreach ($stmt as $row) 
	{ 
		/* do something here */
	}
	

Wrap up

Sql injection is a critical threat and it has to be handled properly to secure the system. Best solution is using pdo with prepared statements. The advantage is, pdo is a wrapper class, its very easy to connect with multiple databases, the code is more organised and maintained.

im krishna Teja, im a computer science engineer by qualification, a physics teacher by profession and a programmer by interest. I'm an expert in building visually stunning web apps using javascript, ...