CODEWITHSUNDEEP
php
TJ is too short
TJ is too short

Reputation: 873

Check if a string contains a disallowed SQL command in PHP

I'm implementing a system that allows to use MySQL queries to filter results. The "problem" is I need to check if the query contains some MySQL commands in order to avoid their execution (commands like drop, delete, truncate, etc). At the moment my code looks like:

$query = "SELECT * FROM thetable";

$notAllowedCommands = array(
    'DELETE',
    'TRUNCATE',
    'DROP',
    'USE'
);
$containsNotAllowedCommands = false;
foreach($notAllowedCommands as $notAllowedCommand){
    $upperCaseQuery = strtoupper($query); 
    if(strpos($upperCaseQuery, $notAllowedCommand) !== false){
        $containsNotAllowedCommands = true;
        break;
    }    
}

The thing is, if I add the following query: 

SELECT * FROM USERS

strpos will find 'USE' (the command) in USERS (table name).

The same if I use aliases or a column name (deleted_records, for example).

Any suggestions on how to avoid this?

Thanks in advance

EDIT After seeing the suggestions this is the solution that I'm going to implement now:

$containsNotAllowedCommands = false;

// Remove unwanted newlines and tabs and replace them with space.
$singleLineQuery = preg_replace("/[\r\n]+/", " ", $query);

// Convert the entire query to upper case (for comparison) and explode
$explodedQuery = explode(' ', strtoupper($singleLineQuery));

// Check if the intersection of both arrays is greater than zero
if(count(array_intersect($notAllowedCommands, $explodedQuery)) > 0){
    $containsNotAllowedCommands = true;
}

I hope can be useful for someone else in the future :D

Thanks guys!

Upvotes: 3

Views: 2787

Answers (1)

Sougata Bose
Sougata Bose

Reputation: 31739

You can try some thing like - 

$notAllowedCommands = array(
    'DELETE',
    'TRUNCATE',
    'DROP',
    'USE'
);
$query = "delete * FROM thetable";

if(preg_match('[' . implode(' |', $notAllowedCommands ) . ']i', $query) == true) {
    echo 'true';
}
else
{
    echo 'false';
}

preg_match will check for those words in the string. The expression would be - [DELETE |TRUNCATE |DROP |USE]i, which will check for those words with a trailing space and it would not be case sensitive.

You can try this also - 

$notAllowedCommands = array(
    'DELETE',
    'TRUNCATE',
    'DROP',
    'USE',
    'delete',
    'drop'
);
$query = "select * FROM users";

$temp= explode(' ', $query);

if(count(array_intersect($notAllowedCommands, $temp)) > 0)
{
    echo "true";
}
else
{
    echo "false";
}

Upvotes: 3

Related Questions