Stay Away from SQL Injections

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.

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

/>

…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. connect_error) { die(“Connection failed: “. $conn->connect_error); } else{ echo “Connected successfully
”; }

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 “
id: “. $row[“userid”]. “- Name: “. $row[“username”]. ““. $row[“status”]. “
”; } } else { echo “0 results
”; }

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 “
id: “. $row[“userid”]. “- Name: “. $row[“username”]. ““. $row[“status”]. “
”; } } 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 “
id: “. $row[“userid”]. “- Name: “. $row[“username”]. ““. $row[“status”]. “
”; } } else { echo “0 results
”; } } else { } echo “
username not accepted
”;

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. connect_error) { die(“Connection failed: “. $conn->connect_error); } else{ echo “Connected successfully
”; } // ********************** 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 “
”; } $result -> free_result(); } // Mark the end of each record set