Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

I was wondering if it is at all possible for a hacker to perform an SQL injection attack using just letters and numbers. For example, let's take a look at this PHP code which uses regular expressions instead of the builtin PHP functions:

<?php
$id = preg_replace('/[^a-zA-Z0-9]/', '', $_GET['id']);
$result = $mysqli->query("SELECT * users WHERE id = '".$id."'");

In case your wondering what the code does, it removes all characters that are not letters or numbers and executes that query it in MySQL. I was wondering if a hacker could be able to exploit that code in some way and if not, what might the difference be between that and mysqli::prepare or mysqli::escape_string?

share|improve this question
    
As a side note: you may use [\W_] instead of that long regex (note the uppercase W), also don't forget to always check if get/post variables are set. You may use something like the following $id = preg_replace('/[\W_]/', '', (isset($_GET['id']) ? $_GET['id']:0));. 0 is the default value if it does not exist. –  HamZa Jul 4 '13 at 11:18
add comment

3 Answers

up vote 6 down vote accepted

There would be only a security flaw if it would be possible to leave the MySQL string literal context the $id value is inserted into and to supply arbitrary SQL fragments. And this is only possible if $id contains the plain ' that would denote the ending delimiter of the MySQL string literal:

// resulting SQL statement with $id = "' OR '1'='1"
SELECT * users WHERE id = '' OR '1'='1'

Now since you’re removing any ' character from $id, it is not possible to leave the string literal context and thus it’s not possible to supply arbitrary SQL fragments.

However, this conclusion applies only for the case the value is used in a MySQL string literal. If you’re using the same input filtering on values in a different query, you still may be vulnerable to SQL injections.

This said, methods like mysqli::prepare and mysqli::escape_string are proper mitigation techniques. mysqli::prepare implements so called parameterized statement/query via prepared statements where the command (SQL statement) and the user supplied data are separated from each other so that the user supplied data cannot be mistakenly interpreted as command. mysqli::escape_string would be similar to you current technique as it just escapes certain characters of data to not being confused as control characters like the string literal ending delimiter.

share|improve this answer
add comment

A general rule of thumb: don't reinvent the wheel!

Although the code you provided is probably not exploitable there are some good reasons to use prepared statements (a good persistence framework is even better) whenever possible:

  • Prepared statements are efficient
  • You don't want to take care of every possible outcome of feeding exotic characters to your regular expression engine (see the mysql_escape_string() fiasco)
  • Relating to the previous point: if the user data is stored/encoded/formated in non-trivial ways SQL smuggling may be possible
  • In your example, defense is implemented in two phases: both the RE and the SQL expression must be tuned with the other component in mind. As your code base starts to grow (and maybe other developers join) it will be difficult to always take care of both steps and you will make an exploitable mistake sooner or later
share|improve this answer
add comment

(I would post this as a comment, but comments don't do links neatly).

This is quite similar to many questions at Stack Overflow.

The Open Web Application Security Project (OWASP) does note that escaping special characters is a defense against SQL injection:

  1. Use Prepared Statements (Parameterized Queries) and Stored Procedures
  2. If a parameterized API is not available...Escape Special Characters
  3. Perform White List Input Validation

As far as proof of concept code that achieves SQLi without special characters - I have not seen any, but absence of evidence is not evidence of absence. If you can implement Parameterized Queries, why not do so?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.