An SQL Injection is a method in which the attacker intentionally gains access to all the information containing in the database through a set of SQL codes. The technique involves injection of SQL commands into an SQL statement. This is done in case the fields such as passwords are too weak or the programmer himself isn’t good enough to secure the users’ sensitive data or both. The SQL statements are text only and so if someone has strong command over programming he or she can execute certain codes that return specific information that they need.
Pay attention to the following codes:
textUserID = getRequestString(“UserID”);
textSQL = “SELECT * from Users WHERE UserID = ” + textUserID;
In the above statements textUserID is the variable that takes the value of UserID. The SELECT statement fetches the information of the UserID (entered by any random user who enters his ID in a website) and stores that in textSQL.
Many hackers use “1=1” as a very powerful method to gain control over vulnerable database. This is because 1=1 is always true and the attackers use this after placing OR. After doing that the statements get executed for sure because one of the statements need to be true.
SELECT * from Users WHERE UserID = 111 OR 1=1;
Most hackers don’t know the inside details of the database. They therefore use this trick. There are high chances that UserID is unknown to the attacker but note that there is also written “OR 1=1”. This means that either UserID needs to be 111 or true. This trick can be used to take out any information. Take another example as under:
SELECT UserID, Name, Password from Users WHERE UserID = 111 OR 1=1;
If the passwords are saved literally into the database in their original form those will be easily retrieved. That is why concepts like encryption and hash methods are used in which the passwords are changed into some other form. Hash methods never bring back the original password and that is why it is the best technique to be applied.
Some hackers also use the trick of “”=”” which results true as well. See the example below to support this:
userName = getRequestString(“Name”);
userPassword = getRequestString(“Password”);
sql = “SELECT * from Users WHERE Name = ‘” + userName + “‘ AND Password = ‘” + userPassword + “‘”;
The single quotation (‘) is SQL lateron. The double quotes (“) are String literals. We need (‘) because we want to provide the DBMS a String.
The above code will look like as under:
SELECT * from Users WHERE Name = “” OR “”=”” AND Password = “” OR “”=””;
In the above statement WHERE “”=”” is always true.
SQL Injection based on Batched SQL Statements
The SQL statements are usually executed one line at a time and send to the server accordingly. When I talk about the batches it means that multiple statements are joined in a single line separated by the semi-colon (;). All the statements are then compiled together as a whole. Most databases support this except few like MySQL. The Batched statement looks as under:
SELECT * from Users; DROP TABLE Employees;
The SELECT * statement will retrieve all the records from the table Users. Right after that the table Employees will get deleted and won’t be restored.
If I have the following server code:
textUserID = getRequestString(“UserID”);
textSQL = “SELECT * from Users WHERE UserID = ” + textUserID;
And the input given in the UserID is 111 then the code will be executed as:
SELECT * from Users WHERE UserID = 111; DROP TABLE Employees;
Steps for protection from SQL Injection
Some database and web developers practice to “blacklist” certain sets of characters or words to prevent from SQL Injection. This isn’t a good practice since there are some characters like semi-colon or double quotation, and words like DELETE or DROP, need to be used in common languages and must be allowed. The SQL Parameters is the only way to protect the database from injection. SQL Parameters are some values that are given to the SQL query while executing it. The parameters are denoted by “@”. See the example below:
textName = getRequestString(“CustName”);
textAddress = getRequestString(“Address”);
textCity = getRequestString(“City”);
textSQL = “INSERT into Customers(CustName, Address, City) values(@0, @1, @2)”;
db.Execute(textSQL, textName, textAddress, textCity);
After reading the above statements the SQL engine will understand that each parameter is correct for its column and to be treated literally and not as part of the SQL to be executed separately. The syntax vary in different programming languages but the logic is same in all.