Terminology

preg_match definition: 'Perform a regular-experssion match.'

Facts, Thoughts and Opinions

Detect malicious code in a SQL statement in PHP

<?
function detectMalice($sql) {
    return preg_match("/;\s*(ALTER|CREATE|DELETE|DROP|EXEC|INSERT|MERGE|SELECT|UPDATE)/",$sql);
}
 
function test($sql) {
    if (detectMalice($sql)) {
        echo "MALICIOUS";
    } else {
        echo "GOOD";
    }
    echo ": $sql
";
}
 
test("SELECT * FROM mytable");
test("SELECT * FROM mytable;");
test("SELECT * FROM mytable;DELETE FROM mytable");
test("SELECT * FROM mytable;    DROP mytable");
test("SELECT * FROM mytable;    
    DROP mytable");
?>

Detecting SQL injection with a regular expression

/\w*((\%27)|(\'))(((\%6F)|o|(\%4F))((\%72)|r|(\%52))|(\%3B)|(;))/ix

Explanation:

  • \w* - Zero or more alphanumeric or underscore characters.
  • (\%27)|\' - The ubiquitous single-quote or its hex equivalent.
  • (\%6F)|o|(\%4F))((\%72)|r|(\%52) - The word or with various combinations of its upper and lower case hex equivalents.
  • (\%3B)|(;) - Semicolon to end the current statement and start a new one.

The use of the union SQL query is also common in SQL Injection attacks against a variety of databases. If the earlier regular expression that just detects the single-quote or other SQL meta characters results in too many false positives, you could further modify the query to specifically check for the single-quote and the keyword union. This can also be further extended to other SQL keywords such as select, insert, update, delete, etc.

Note that you would not want to use this to actually alter a user's input. This is best used in validating a text box or password field where there is no guarantee that you will not be able to parameterize the outbound query.

Images

[[/div]]
  •   Subtopics

  •   Writings