SHARE

Preventing SQL Injections

The best practice to prevent SQL injections is to validate and clean variables that are obtained through the script’s URL.

For example, if the script expects the variable to always be a number, we can easily add a command to only accept the variable if it is numeric. For example, in PHP we could have something like:

if (isset($_GET[‘id’])) {

                $id=intval($_GET[‘id’]);

                }

If (!$id) {

                header( “HTTP/1.0 404 Not Found” );

                exit();

                }

The function “intval” will force the variable to be numeric, so if the hacker types in any command in the hopes of trying to perform an SQL injection, the command will simply be ignored and the script will terminate giving the error 404 (page not found).

If you are expecting the contents of the variable to be alphanumeric, you should have some form of validation, where only allowable values can be passed on. One simple way to do that is with “if” statements, where unknown values will simply be ignored and the code will not be run for unknown values.

As for the login/password situation described on the previous page, the basic idea is to escape the string, so single quote and double quote characters are preceded by a backslash and, therefore, ignored (the OR 1=1 will be now considered part of the login or password, and not a separate clause). In PHP, this could be accomplished with something like:

if (isset($_POST[‘login’])) {

                $login=addslashes($_POST[‘login’]);

                }

if (isset($POST[‘password’])) {

                $password=addslashes($_POST[‘password’]);

                }

Another important practice to prevent SQL injections is to use variables inside quotes in queries. For example, instead of:

SELECT title,content FROM articles WHERE id=$id;

Use:

SELECT title,content FROM articles WHERE id=’$id’;

In fact, if you do not do that, the hacker will be able to bypass the login and password by adding OR 1=1 to the password field even if you add the code to escape quotes.

There are several other ways to perform an SQL injection and also several other ways to prevent them. The goal of this tutorial was to get you acquainted with the problem to see if your website has this kind of vunerability, not to be a complete guide on the subject.

1
2
3

Gabriel Torres is a Brazilian best-selling ICT expert, with 24 books published. He started his online career in 1996, when he launched Clube do Hardware, which is one of the oldest and largest websites about technology in Brazil. He created Hardware Secrets in 1999 to expand his knowledge outside his home country.