Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

PHP - Prevent SQL Injection

abolinhasDevOps and System Administrator
CERTIFIED EXPERT
Published:
Updated:
SQL injection vulnerabilities have been described as one of the most serious threats for Web applications. Web applications that are vulnerable to SQL injection may allow an attacker to gain complete access to their underlying databases.

Because these databases often contain sensitive consumer or user information, the resulting security violations can include identity theft, loss of confidential information, and fraud. In some cases, attackers can even use an SQL injection vulnerability to take control of and corrupt the system that hosts the Web application.

SQL injection refers to a class of code-injection attacks in which data provided by the user is included in an SQL query in such a way that part of the user's input is treated as SQL code.By lever-aging these vulnerabilities, an attacker can submit SQL commands directly to the database. These attacks are a serious threat to any Web application that receives input from users and incorporates it into SQL queries to an underlying database.

The cause of SQL injection vulnerabilities is relatively simple and well understood: insufficient validation of user input.

______________________________

Types of SQL injection attacks:
______________________________

Union Query:
In union-query attacks, an attacker exploits a vulnerable parameter to change the data set returned for a given query. With this technique, an attacker can trick the application into returning data from a table different from the one that was intended by the developer.

Attackers do this by injecting a statement of the form: UNION SELECT <rest of injected query>.

Because the attackers completely control the second/injected query, they can use that query to retrieve information from a specified table. The result of this attack is that the database returns a dataset that is the union of the results of the original first query and the results of the injected second query.
SELECT accounts FROM users WHERE login='' UNION
                      SELECT cardNo from CreditCards where
                      acctNo=10032 -- AND pass='' AND pin=

Open in new window

Assuming that there is no login equal to , the original first query returns the null set, whereas the second uery returns data from the CreditCards table. In this case, the database would return column cardNo for account 10032. The database takes the results of these two queries, unions them, and returns them to the application.
In many applications, the effect of this operation is that the value for cardNo is displayed along with the account information.

Piggy-Backed Queries:
In this attack type, an attacker tries to inject additional queries into the original query. We distinguish this type from others because, in this case, attackers are not trying to modify the original intended query; instead, they are trying to include new and distinct queries that piggy-back on the original query. As a result, the database receives multiple SQL queries.

The first is the intended query which is executed as normal; the subsequent ones are the injected queries, which are executed in addition to the first. This type of attack can be extremely harmful. If successful, attackers can insert virtually any type of SQL command, including stored procedures,1 into the additional queries and have them executed along with the original query.
SELECT accounts FROM users WHERE login='doe' AND
                      pass="; drop table users -- ' AND pin=123

Open in new window


Magic String:
The magic string is a simple string of SQL used primarily at login pages. The magic string is
'OR''='
                      

Open in new window

When used at a login page, you will be logged in as the user on top of the SQL table.

Alternate Encodings:
In this attack, the injected text is modified so as to avoid detection by defensive coding practices and also many automated prevention techniques. This attack type is used in conjunction with other attacks. In other words, alternate encodings do not provide any unique way to attack an application; they are simply an enabling technique that allows attackers to evade detection and prevention techniques and exploit vulnerabilities that might not otherwise be exploitable. These evasion techniques are often necessary because a common defensive coding practice is to scan for certain known bad characters, such as single quotes and comment operators.
SELECT accounts FROM users WHERE login='legalUser';
                      exec(char(0x73687574646f776e)) -- AND pass=" AND pin=
                      

Open in new window


Inference:
In this attack, the query is modified to recast it in the form of an action that is executed based on the answer to a true/- false question about data values in the database. In this type of injection, attackers are generally trying to attack a site that has been secured enough so that, when an injection has succeeded, there is no usable feedback via database error messages.

Since database error messages are unavailable to provide the attacker with feedback, attackers must use a different method of obtaining a response from the database.

There are two well-known attack techniques that are based on inference:

Blind Injection: In this technique, the information must be inferred from the behavior of the page by asking the server true/- false questions.
SELECT accounts FROM users WHERE login='legalUser'
                      and 1=0 -- ' AND pass=" AND pin=0
                      SELECT accounts FROM users WHERE login='legalUser'
                      and 1=1 -- ' AND pass=" AND pin=0
                      

Open in new window


Timing Attacks: A timing attack allows an attacker to gain information from a database by observing timing delays in the response of the database. This attack is very similar to blind injection, but uses a different method of inference.
SELECT accounts FROM users WHERE login='legalUser' and
                      ASCII(SUBSTRING((select top 1 name from sysobjects),1,1))
                      > X WAITFOR 5 -- ' AND pass=" AND pin=0
                      

Open in new window


________________________

Defenses from SQL Injection attacks
________________________

To defend against SQL Injection attacks, user input must not directly be embedded in SQL statements. Instead, you can use parameterized statements, and Escaping functions to check user input and storage procedure.

Using Parameterized Statements:
Prepared queries (also called prepared statements) solve a great many of the aforementioned risks. Prepared queries are query templates: the structure of the query is pre-defined and fixed and includes placeholders that stand-in for real data.

The placeholders are typically type-specific (for example, int for integer data and text for strings) which allows the database to interpret the data strictly. For instance, a text placeholder is always interpreted as a literal, avoiding exploits such as the query stacking SQL injection. A mismatch between a placeholder's type and its incoming datum causes execution errors, adding further validation to the query.

In PHP version 5 and above, you have multiple choices for using parameterized statements. PDO database layer is one of them:
$db = new PDO('pgsql:dbname=database');
                      $stmt = $db->prepare("SELECT priv FROM testUsers WHERE username=:username AND password=:password");
                      $stmt->bindParam(':username', $user);
                      $stmt->bindParam(':password', $pass);
                      $stmt->execute();
                      

Open in new window

Alternatively, you could use a vendor-specific method. For example in MySQL 4.1 and above with the mysqli extension.
$db = new mysqli("localhost", "user", "pass", "database");
                      $stmt = $db -> prepare("SELECT priv FROM testUsers WHERE username=? AND password=?");
                      $stmt -> bind_param("ss", $user, $pass);
                      $stmt -> execute();
                      

Open in new window


Using Escaping:
A straight-forward, though error-prone way to prevent injections is to escape dangerous characters. One of the reasons for it being error prone is that it is a type of blacklist which is less robust than a whitelist. For instance, every occurrence of a single quote (') in a parameter must be replaced by two single quotes ('') to form a valid SQL string literal. In PHP, for example, it is usual to escape parameters using the function mysql_real_escape_string before sending the SQL query.

1. mysql_real_escape_string escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.
2. mysql_real_escape_string() calls MySQL's library function 3. mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.
<form  enctype="application/x-www-form-urlencoded" 
                      method="post" action="<?php $_SERVER['PHP_SELF']?>">
                      <input type="text" name="name" value="">
                      <input type="submit" value="submit">
                      </form>
                      <?php
                      $link = mysql_connect('localhost', 'root', '');
                      if (!$link) {
                          die('Could not connect: ' . mysql_error());
                      }
                      $item = $_POST['name'];
                      $escaped_item = mysql_real_escape_string($item, $link);
                      echo"Escaped string: \n", $escaped_item; 
                      // input: Zak's and Derick's Laptop
                      // output:  Zak\'s and Derick\'s Laptop
                       
                      // input: a';DROP TABLE users; SELECT * FROM data WHERE name LIKE
                      // output: a\';DROP TABLE users; SELECT * FROM data WHERE name LIKE  
                      ?>
                      

Open in new window

mysql_real_escape_string does not always guarantee data safety. Certain queries can still permit SQL injection, even after escapes are applied. When executing integer expressions, it's not necessary to enclose the value inside single quotes. Consequently, the semicolon character is sufficient to terminate the query and inject an additional query.

Most techniques for the treatment of SQL Injection refer to the functions of escape, but few know that these techniques are ineffective to prevent such an attack. The simple addition of a backslash ( "\") in a string containing an apostrophe ( ') will not eliminate the possibility of exploitation of attacks based on SQL Injection

Now I will show you a good example to prevent sql injection
<?php 
                      function anti_injection($sql){
                         $sql = preg_replace(sql_regcase("/(from|select|insert|delete|where|drop table|like|show tables|\'|'\| |=|-|;|,|\|'|<|>|#|\*|--|\\\\)/"), "" ,$sql);
                        
                         $sql = trim($sql);
                         $sql = strip_tags($sql);
                         $sql = (get_magic_quotes_gpc()) ? $sql : addslashes($sql);
                         return $sql;
                      } 
                      ?>
                      <form  enctype="application/x-www-form-urlencoded" method="post" action="<?php $_SERVER['PHP_SELF']?>">
                      <input type="text" name="name" value="">
                      <input type="submit" value="submit">
                      </form>
                      <?php 
                      $name = anti_injection($_POST['name']);
                      echo $name 
                      // input: Zak's and Derick's Laptop
                      // output: Zaks and Dericks Laptop
                       
                      // input: a';DROP TABLE users; SELECT * FROM data WHERE name LIKE 
                      // output: a users data name  
                      ?>
                      

Open in new window

In this example the anti_injection function clear user input (if it finds any of the values above)

Unlike the mysql_real_escape_sting that filters only the following characters \ x00, \ n, \ r, \, ', "and \ x1a, this enables not only filter the same characters as the mysql_real_escape_string but filtering words like" DROP TABLE ", "LIKE", "INSERT" and more.

One of the great advantages of this example is that you can add more characters and words to the function to be filtered, just to put it between || the value name. Example: |drop table|\'|*|+|_|like|

Conclusion:
Many web developers are unaware of how SQL queries can be tampered with, and assume that an SQL query is a trusted command. It means that SQL queries are able to circumvent access controls, thereby bypassing standard authentication and authorization checks, and sometimes SQL queries even may allow access to host operating system level commands.

Direct SQL Command 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 a SQL query.

The following examples are based on true stories, unfortunately. Owing to the lack of input validation and connecting to the database on behalf of a superuser or the one who can create users, the attacker may create a superuser in your database.

You may plead that the attacker must possess a piece of information about the database schema in most examples. You may be right, but you never know when and how it can be taken out, and if it happens, your database may be exposed.

If you are using an open source, or publicly available database handling package, which may belong to a content management system or forum, the intruders can easily produce a copy of a piece of your code. It may be also a security risk if it is a poorly designed script.

These attacks are mainly based on exploiting the code not being written with security in mind.

My advice:
Never trust any kind of input, especially that which comes from the client side, even though it comes from a select box, a hidden input field or a cookie.  

Regards

André Bolinhas
19
16,102 Views
abolinhasDevOps and System Administrator
CERTIFIED EXPERT

Comments (8)

Sory, But ERROR ???

$return1=  anti_injection("selectABC");
echo $return1;

---> result is ABC

So, if username = selectABC , it trim all select, only still ABC ---> Wrong?
CERTIFIED EXPERT

Commented:
nice
Vrey nice - thank you. :)

Commented:
The article is good overall, except your last example. The added protection in your last example is based on a blacklist approach, which is not a good recommendation.

If you need to use user input in data values of your query (WHERE fields or LIMIT), prepared statements should be enough to prevent SQLi. If you really need to use user input elsewhere, you should validate the user input (if you are expecting an integer then only allow the query execution if the parameter is an integer) and you should also escape the user input.

To escape user input, you shouldn't use the function addslashes() as in your example. You should use the DBMS specific escape function, such as mysql_real_espace_string() because it takes into account the charset set by the connection to the database. This prevents certain types of attacks with unicode charsets where the behavior of addslashes() differs from how the DBMS will interpret the query.
nice thanks

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.