SQL injection is a common hacking technique that places malicious code in SQL statements, via Web page input. It can destroy your database. Three SQL injection vulnerabilities and their remedies are discussed in this article.
SQL injection is a code injection technique that exploits the security vulnerability of the connected database layer of an application. This vulnerability allows poorly filtered user inputs with undesirable escape characters embedded within SQL statements to get executed by the database server. User inputs with an inappropriate data type also create an SQL injection vulnerability problem. Unfiltered user input through a Web page is a common source of this type of security breach, as it makes the database accessible to hackers to gain control over it. This security loophole generally occurs when one programming or scripting language is embedded inside another. SQL injection vulnerability is also known as SQL insertion attack.
SQL injection typically occurs when a user-supplied input, like the name, is purposefully modified to a specific MySQL statement to get executed within the Web database. For example, a user form for user name may be designed to enter the user’s name for credential verification. But instead of a simple alphanumeric literal string, a user may provide a string to delete a table, as shown in Figure 1.
With this form input entry, the server will drop the entire user’s table. This is an example of how commanding and hazardous an SQL injection can be and shows the necessity of regular database backups.
Here I shall discuss only three of the SQL injection features and their possible remedies. These are: bypassing query logic, retrieving protected data and multiple query injections. All these vulnerabilities are internal to SQL and intrinsic to embedded programming structures but arise mainly due to careless coding. Proper systematic goal-oriented design methodology can reduce the chance of these to a great extent and make the database secure from external attacks.
Retrieving hidden data
SQL injection can be demonstrated directly on SQL database prompt. For example, if we consider an SQL query to browse a user table for a user name, and assign a user name variable string as:
“HelloWorld” or 1=1 - ‘, the SQL SELECT statement: set @id1 := “HelloWorld”, @id2 :=”1=1--” ; select * from users where username= @id1 or @id2;
…it will display all records from the user’s table irrespective of the value of the user name field. Even if the user name ‘HelloWorld’ is absent in the user table, all the records will still be displayed.
…it will display all records from the user’s table irrespective of the value of the user name field. Even if the user name ‘HelloWorld’ is absent in the user table, all the records will still be displayed.
Retrieving hidden data: Web page mode
The above vulnerability can be explored through a Web-based login module to bypass a user’s name pattern matching restriction. For example, if there is a login form login.html as shown below:
login.html <form action=’login.php’ method=”post”> <input type=”name” name=”username” required=”required”/>/> <input type=”submit” value=”Submit”/> </form>
…then a PHP Web page program login.php may be written to exploit the SQL injection loophole of the SQL query. The purpose of this code is to read the user’s name from the users’ table of the database. This code has four parts. Initially, it establishes a connection to a MySQL database customer; then it reads the variable user name from the login.html Web form and performs the first query to read a particular user’s name from the database table users. Subsequently, this section of the code displays all the records with the user name = $umame. The third section performs another query to display the same user’s name but with restriction status. In a normal situation, this section will display all records having the user name =$uname, provided the record satisfies the condition status=1. Lastly, it closes the database connection and returns the control to the system.
Step 1: Open a database customer.
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$database = “customer”;
// Create a connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die(“Connection failed: “ . $conn->connect_error);
}
else{
echo “Connected successfully<br>”;
}
Step 2: Read the POST variable user name from the Web form and perform the SQL select query.
$uname = $_POST[‘username’] ;
#1. Read unrestricted records
$sql = “SELECT * FROM users WHERE username = ‘”. $uname;
$result = mysqli_query($conn,$sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo “<br>id: “ . $row[“userid”]. “ - Name: “ . $row[“username”]. “ “ . $row[“status”]. “<br>”;
}
}
else {
echo “0 results <br>”;
}
Step 3: Perform the select query with restriction status. Field status will allow only those records that have status=1.
# 2 Read restricted records
$sql = “SELECT * FROM users WHERE username = ‘”. $uname. “ and status = 1”;
$result = mysqli_query($conn,$sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo “<br>id: “ . $row[“userid”]. “ - Name: “ . $row[“username”]. “ “ . $row[“status”]. “<br>”;
}
} else {
echo “0 results”;
}
Step 4: Close the connection and the PHP Web page login.php.
$conn->close(); ?>
With this code snippet, one can use the given Web based form to exploit the SQL injection technique to bypass the user name verification clause of the SQL query statement. The query statement:
$sql = “SELECT * FROM users WHERE username = ‘”. $uname;
…can inject a string into the statement to browse the entire table and display all the records of the table. To make the query TRUE for all the situations one can enter the string ‘or 1=1 (Figure 2) instead of a valid user’s name. Since 1=1 is always true and the connecting logical operator is OR, it will make the query TRUE for all the records and the subsequent for-loop will fetch all the records from the table users (Figure 3). If proper precaution is not taken, this type of injection can make the database vulnerable to hackers.
Retrieving restricted data: Web page mode
To retrieve restricted records as shown in the second query, it is required to bypass the restriction status = 1 clause of the query statement. This can be done by commenting the restriction with an SQL injection statement parker ‘— (Figure 4), where ‘parker’ is a valid user’s name. String ‘– will make the rest of the line commented and the query will display all records with the user name = parker (Figure 5).
Prevention: Both the above-mentioned injections can be prevented by restricting the input field length to a given suitable number and allowing an alphanumeric field value, as shown in the following code snippet:
if (preg_match(“/^\w{6,20}$/”, $_POST[‘username’]))
{
$sql = “SELECT * FROM users WHERE username=’”.$uname.”’”;
echo $sql;
$result = mysqli_query($conn,$sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo “<br>id: “ . $row[“userid”]. “ - Name: “ . $row[“username”]. “ “ . $row[“status”]. “<br>”;
}
}
else {
echo “0 results <br>”;
}
}
else
{
echo “<br>username not accepted<br>”;
}
The regular expression pattern matching function preg_match() will filter out any non-alphanumeric character within the input string and will allow a string of length 6 to 20 characters only. For an input field value ‘parker’, the above filter and the subsequent query string will generate a query string as:
SELECT * FROM users WHERE username=’parker’
…and will produce the following browsing list:
id: 115 - Name: parker 1 id: 135 - Name: parker 0
Multiple query restriction
One dangerous SQL injection menace is the multiple query statement in single query execution. This injection is harmful in the sense that one can inject a destructive data manipulation query along with a simple SELECT query statement. One can add multiple SQL statements with a SELECT statement as follows:
“parker’; DELETE FROM users;”;
A multiple query statement can be injected into a Web based PHP routine through an HTML form as used earlier. The form can read multiple query statements and pass them into the server-side routines. Here is an example of multiple query injection by a Web form and its form routine. For clarity, the entire code is given. Those who are interested can experiment with this code for further study.
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$database = “customer”;
// Create a connection
$conn = new mysqli($servername, $username, $password,$database);
// Check the connection
if ($conn->connect_error) {
die(“Connection failed: “ . $conn->connect_error);
}
else{
echo “Connected successfully<br>”;
}
//**********************Main Body *****************************
$uname = $_POST[‘username’];
$sql = “SELECT * FROM users WHERE username=’”.$uname;
if ($conn -> multi_query($sql)) {
do {
// Store first result set
if ($result = $conn -> store_result()) {
while ($row = $result -> fetch_row()) {
//Display only first two fields
printf(“%s,%s\n”, $row[0],$row[1]);echo “<br>”;
}
$result -> free_result();
}
// Mark the end of each record set
if ($conn -> more_results()) {
printf(“-------------\n”); echo “<br>”;
}
//Move to next record set
} while ($conn -> next_result());
}
$conn->close();
?>
This code will get its input variable through $_POST and will make a query statement $sql. If the input variable contains multiple SQL statements separated by ‘;’ then the query statement $sql will be divided into separate query strings by the multi_query() method of the connection object $conn. The stored results of each SQL statement will be fetched and displayed on the screen by an iterative while loop.
if ($result = $conn -> store_result()) {
while ($row = $result -> fetch_row()) {
//Display only first two fields
printf(“%s,%s\n”, $row[0],$row[1]);echo “<br>”;
}
At the end of a record set of a stored result, the loop will iterate to the next stored result set and the browsing on the selected tables will continue for all the successfully executed stored result sets. For example, if the form input with three SQL queries is:
parker’; select * from users; select * from indentreg
…then the above PHP routine executes all of them in three iterations of the do-while loop. First, the record sets of the user’s table for user name parker will be displayed. In the subsequent two iterations, it will browse through the users’ and indentreg tables. In each iteration, queries will display only the first two fields of the record sets.
115, parker 135, parker ------------- 100,dipankar 110,avishek 120,demo 115,parker … … ------------- 1,003 2,002 3,001 4,005
This problem of injecting multiple SQL statements to the currently active database on the server associated with an open file pointer is not there in mysqli_query(), as it does not support multiple statements.
Prevention: The best remedy is the restriction of the length of the input field. Next, verify the input string for data type and strictly try to adhere to the required format. Finally, the input fields should be specific to table fields, and verification of each field should be done before passing the variable to its server-side execution platform. The server-side platform should also have a proper filtering mechanism to prevent any unfortunate incident within the database.
MySQL function mysql_real_escape_string() is a good option to prevent SQL injection vulnerability. It is used to escape special characters in a string to be used in an SQL statement. This function creates a legal SQL string that can be used in an SQL statement. With the help of this function, the given input string is encoded to produce an escaped SQL string. It takes the current character set of the connection into account while preparing a valid string. Here is an example of the use of this function:
$uname = $conn->real_escape_string($_POST[‘username’]) ;
$sql = “SELECT * FROM passwdOLD WHERE username = ‘$uname’”;
$result = mysqli_query($conn,$sql);
// output data of each row
while($row = $result->fetch_assoc()) {
echo “<br>id: “ . $row[“userid”]. “ - Name: “ . $row[“username”]. “ “ . $row[“status”]. “<br>”;
}
With the help of this function any input field variable of the form ‘or 1=1 or parker’— will fail to produce a legal SQL statement and only parker will produce a proper result.
We have discussed the three most common SQL injection vulnerabilities that are a real threat to the database server. There are a few more types of SQL injections too that can cause serious problems.
