This post will explain about sql injection and different ways to prevent it.
SQL injection is a technique where malicious SQL code is passed from the input field of the frontend to the backend of database-driven application.
All the application now days are database-driven which means that user has to log-in in an application to properly use it?s functionality.
SQL injection usually occurs when you ask a user for input, like their username, password and instead of username and password the user gives you an SQL statement that you will unknowingly run on your database
Different examples are,
The hacker attack shown figure 1 will have the following execution at the server side.
SELECT * FROM USER_TABLE WHERE Username= ?Brian? and Password=? ? or 1 = 1 ? ?
1= 1 is always true so this query will return all the users information from the table.
The hacker attack shown figure 2 will have the following execution at the server side.
SELECT * FROM customers WHERE username = ?Brian? and Password= ? ? ; DELETE FROM customers WHERE 1 or username = ? ?
If the hacker is able to run this query at the server the customer table will become empty and all the records will be gone.
Prevention of the SQL injection
There are different methods proposed to prevent the sql injection which are as follows.
What mysql_real_escape_string does is take a string that is going to be used in sql query and return the same string with all sql Injection attempts safely escaped. Basically, it will replace those troublesome quotes(?) a user might enter with the backslash ().
//The injection shown in figure 1,$sql_injected_password = “‘ OR 1′”;//The injection shown in figure 2, $sql_injected_password = “‘; DELETE FROM customers WHERE 1 or username = ‘”;//Using the mysql_real_escape String$safe_password = mysql_real_escape_string($password);
By using the mysql_real_escape_string the troublesome quotes (?) shown in figure 1,2 will be replaced by the backslash () shown below,
SELECT * FROM customers WHERE username = ?Brian? and Password= ?? OR 1??
SELECT * FROM customers WHERE username = ?Brian? and Password= ??; DELETE FROM customers WHERE 1 or username = ??
This method provide the protection against the sql injection but it is not very reliable. It is because hacker can make a combination that will by-pass the mysq_real_escape_string method. No, matter how much escape you made at the server side but still there are many possibilities and combinations hacker can think of.
Note: mysql_real_escape_string was depreciated in php 5.5.0 and is removed from 7.0.0. mysql has been replaced by mysqli. so the method becomes mysqli_real_escape_string()
Using Prepared Statements (with Parameterised Queries)
The right way to prevent SQL injection is by using parameterised queries. This means defining the SQL code that is to be executed with placeholders for parametric values, programmatically adding the parameter values, then executing the query.
It includes three steps,
- Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled ???). Example: INSERT INTO MyGuests VALUES(?, ?, ?)
- The database parses, compiles, and performs query optimisation on the SQL statement template, and stores the result without executing it
- Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values
$conn = new mysqli($servername, $username, $password, $dbname);$stmt = $conn->prepare(“INSERT INTO MyGuests (username, password) VALUES (?, ?)”);$stmt->bind_param(“ss”, $_POST[‘username’];, $_POST[‘password’]);$stmt->execute();$stmt->close();
The code above is described as,
$conn->prepare(“INSERT INTO MyGuests (username, password) VALUES (?, ?)”);
The (?) present in the sql statement shows where the parametric values will be substituted. It can be integer, string, double or bob value.
$stmt->bind_param(“ss”, $username, $password);
The bind method will tell the sql what the parameters are.
The ?ss? argument tell the sql about the parameter type.
The parameter may be one of four types:
- i ? integer
- d ? double
- s ? string
- b ? BLOB
Then the values are assigned to the parameters and query is executed.
Using prepared statement and parametrised query the sql injection can be prevented because the parameter values (even if they are injected SQL) won?t be executed because they are not part of the query statement and it will be treated as user input in the form of string, not SQL code. In other words, the query will look for a user with this password instead of executing unexpected sql code.