Spiga

How to prevent SQL Injection

by Gabi Solomon

SQL injections are a common vulnerability in web-based applications that use databases.

Many web developers are unaware of how SQL queries can be tampered with, and assume that an SQL query is a trusted command. This means that SQL queries are able to go pass access controls, thereby bypassing standard authentication and authorization checks the web aplication might have.
Plus many of them think : Who will hack my website ? What reason would some have to hack my website ?

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.

Deleting an entire table

As an example of a potential SQL injection, consider a login form asking only for a username, where the backend has code reading:

PLAIN TEXT
PHP:
  1. mysql_query('SELECT * FROM user WHERE username = "' . $_GET['username'] . '");

A malicious hacker could attempt to enter the value ""; DELETE FROM user WHERE 1", which would have the effect of removing all users in the table.

PLAIN TEXT
SQL:
  1. SELECT * FROM user WHERE username = ""; DELETE FROM user WHERE 1 ;

Granted, this won't happen with PHP's mysql extension as it will not execute multiple queries by default; this is just an illustration.

Reseting a password / getting more privileges

Consider this simple pasword changing query :

PLAIN TEXT
PHP:
  1. $query = "UPDATE user SET password="'.$_GET['password'].'" WHERE id="'.$_GET['user_id'].'" ;

A bad intentiond user can use this vulnerable query to get admin privilages :

PLAIN TEXT
PHP:
  1. // user_id == " or username like '%admin%'; --
  2. $query = "UPDATE user SET password='...' WHERE id="" or username like '%admin%'; --";

How to prevent SQL injection

You might think that the atacker must have the mysql database structure in order to do this attacks. You are right but there are numerous way how he could find that out: exposed mysql_error messages, you are using an open_source script, simple try and error since many web-developers use the same table names and field names, espacialy at the user tables.

Here is an example of table guessing injection attack :

PLAIN TEXT
SQL:
  1. SELECT ...  FROM TABLE WHERE id = '$user_id';
  2. SELECT ...  FROM TABLE WHERE id = '' AND 1=(SELECT COUNT(*) FROM tabname); --';

After a few trial and error they can find a table name ;) .

In order to prevent this the first line of defence is to build you're aplication with security in mind. This means to use some precaution methods :

  • Never conect to the database using a super user. Try to limit the user you use to the priviliges he needs and nothing more.
  • Always check if the user input is what you expected. Use the simple php functions ( eg is_numeric() ) or even validating using regular expresions.
  • Transform any fields to the type you would espect :
    PLAIN TEXT
    PHP:
    1. settype($offset, 'integer');
    2. $query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;"; // please note %d in the format string, using %s would be meaningless
    3. $query = sprintf("SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET %d;",
    4. $offset);

  • Limit User Inputs length.Althoug this is not going to stop all SQL injection but it's going to limit them a little :)
  • Do not print on screem any error mesages from mysql, because this can expose the mysql structure you might have thought the attacker doesn't know.
  • And here is an example of a "Best Practice" SQL query right out of the PHP Manual :
    PLAIN TEXT
    PHP:
    1. // Quote variable to make safe
    2. function quote_smart($value)
    3. {
    4. // Stripslashes
    6. $value = stripslashes($value);
    7. }
    8. // Quote if not integer
    9. if (!is_numeric($value)) {
    10. $value = "'" . mysql_real_escape_string($value) . "'";
    11. }
    12. return $value;
    13. }// Connect
    14. $link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    15. OR die(mysql_error());// Make a safe query
    16. $query = sprintf("SELECT * FROM users WHERE user=%s AND password=%s",
    17. quote_smart($_POST['username']),
    18. quote_smart($_POST['password']));

Hope this is helpfull and you were able to learn to protect you're web aplication against SQL Injection Attacks

Related Posts