SQL Injection and PHP

In my opinion the most commonly used database attack is SQL Injection. The attack is used by inserting a SQL query into a parameter using the URL field, to be executed by the database server. Being able to execute SQL commands on the database server makes it possible to get control under the server. The following material requires basic knowledge of the SQL-92 language, because this is the main language for accessing data.

SQL injection represents one of the greatest threats for the following reasons:

Developers are so lazy. They do not test their applications on errors. Divelopers hopes to skills but everybody be able to mistakes.
Many websites are exposed to SQL injection attacks but don't know it.
Databases often house significant persanal data such as credit card numbers.
Any website has a database on the back end and use SQL query to access to the data.

Let's consider how SQL Injection is carried out, that is, how hackers or security experts search for this vulnerability on the server and their actions afterwards. Suppose that you have a database containing a table Users comprised of three fields: id, name, and password. A query to fetch records from this table may look as the following:

 SELECT * 
 FROM Users
 WHERE id = $id

In this query the value of the id field is compared with the value of the $id variable. If the value of this variable is obtained as a script parameter from the URL or a cookie and it is not checked for prohibited characters, the query becomes vulnerable for SQL Injection. A hacker may use the parameter to inject malicious code.

Consider how a query can be modified by the hacker. Your script searches for a string with the user parameters by its identifier. But sending text 10 OR name="Administrator" as the $id variable will make the query look as follows:

 SELECT * 
 FROM Users
 WHERE id = 10 OR name="Administrator"

This query will return not only the record whose id field is 10, but also the record whose name field is Administrator. In this way, the hackers will be able to see the administrator's password and obtain access to restricted data. To prevent the problem, you should have a clear idea of what data are stored in table fields, and allow users send as parameters only allowed data. For example, the id field is numerical; therefore, the value of its variable can only be comprised of digits from 0 to 9. The $id variable can be processed as is shown in the following example:

 <form action="db1.php" method="get">
  <input name="id">
 </form>

 <?php 
  $id=preg_replace("/[^0-9]/", "", $id);
  print('SELECT * FROM Users WHERE id='.$id);
 ?>

When loaded in the web browser, this code creates a form to enter the identifier by which to conduct a search. When the data from the form is passed to the script, the following line is executed before the data is searched for in the table:

 $id=preg_replace("/[^0-9]/", "", $id);

It replaces all characters in the $id variable that are not digits from 0 to 9 with a null value. Thus, if you enter, for example, 10 or name="Administrator" in the form, this code line will strip all non-digit characters from it, leaving only 10 to be passed to the query.

String variables supplied by users should be given the same treatment. Suppose that the database is searched by the name field. How can hackers be prevented from entering characters that may allow them to break into the database? Strings can contain alphanumerical characters and, if the field allows multi-word entry, spaces; therefore, the code for filtering the parameter entered by users into this field should look as follows:

 <form action="db2.php" method="get">
  <input name="name">
 </form>

 <?php 
  $name=preg_replace("/[^a-zA-Z0-9 ]/i", 
     "", $name);
  print('SELECT * FROM Users WHERE name='.$name);
 ?>

This code replaces everything that is not a letter, digit, or space with null value. Thus, double and single quote characters are prohibited, and if hackers try to send, for example, string 10 OR name="Administrator," only 10 OR name=Administrator will be placed into the query. This string violates the query format and will be rejected.

If you are certain that only a single word can be entered into the input field, you can forbid space characters from being sent to the script from this field.

The regular expression "/[^a-zA-Z0-9 ]/i" prohibits any special characters; however, sometimes it is necessary to use a certain special character. For example, a record filed may contain the [ and ] characters, and you will have to allow users enter these. While these particular characters will do no harm in a SQL request, far from all special characters are this innocent.

A query can be constructed with the values either enclosed in single quotes or not. For example, the following two queries are identical:

 SELECT *
 FROM Table
 WHERE id=1

and

 SELECT *
 FROM Table
 WHERE id='1'

In the first query, the id field is compared to the value of 1, and in the second query, the number is enclosed into single quotes. Single quotes are mandatory if the value contains a space, that is, is a string; I, however, recommend using them always. Why? Let's suppose that instead of the value of 1, a variable is used whose value is supplied by users. If hackers send SQL control characters as such value, the query will look as the following:

 SELECT *
 FROM Table
 WHERE id='_?--%='

Save your comment

Can you use BBCode? You can use [quote] to quote, [b] and [i] for text decoration. You can't use any other codes.

Low cost auto insurance quotes

fast cash loan . online duty free

Your name:
Comment:
Protection code: