What is SQL Injection and how to prevent SQL injection in PHP

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

What is SQL Injection

SQL 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. The following examples are based on true stories, unfortunately.

Suppose you are taking raw user input and inserted it into a MySQL database, then there's a chance that you have left yourself wide open for a security issue. Let's take below example in PHP.

// create connection
$connection = new mysqli($servername, $username, $password, $dbname);

// get username
$name = $_POST['name'];

$query = "SELECT * FROM customers WHERE username = '$name'";
$result = $connection->query($query);

Now suppose what will be query if user input name as "OR 1".

$query = "SELECT * FROM customers WHERE username = OR 1";

This will give user get access to databse. Same way user can also manipulate database like reset password without knowing old password.

// password update query.
$query = "UPDATE users SET password = '$password' WHERE username = '$username';";

But if user submits the value ' OR username like'%admin% to $username to change the admin's password, or simply sets $password to 123456' to gain more privileges. The sql query will be twisted as:

$query = "UPDATE users SET password = '123456' WHERE username = '' OR username like '%admin%';";

how to prevent SQL injection

You basically have two options to avoid using SQL Injection:

1. Use prepared statements.

These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

$statement = $connectio->prepare('SELECT * FROM users WHERE name = ?');
$statement->bind_param('s', $name); // 's' specifies the variable type => 'string'

$statement->execute();

$result = $statement->get_result();

For insert query prepared statement:

$statement = $dbh->prepare("INSERT INTO users (name, password) VALUES (:name, :password)");
$statement->bindParam(':name', $name);
$statement->bindParam(':password', $password);

// insert row
$name = 'mvuser';
$password = 'mvpassword';
$statement->execute();

2. Use mysql_real_escape_string() PHP function

mysql_real_escape_string() funcion escapes special characters in a string for use in an SQL statement. 

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.

// connect to database
$connection = new mysqli($servername, $username, $password, $dbname);

// get username
$name = mysql_real_escape_string($_POST['name']);

$query = sprintf("SELECT * FROM customers WHERE username = '$name'");
$result = mysql_query($query) or die (mysql_error());

PHP has a wide range of input validating functions, so always check if the given input has the expected data type. Always create customized users with very limited privileges. Never connect database with use superuser.

There are other measures you can take and prevent malicious user attack. Happy coding!