Vulnerability demo(php+mysql+apache)

test.php

<?php 
$id=$_GET['id'];
$conn=new mysqli('localhost','root','root','test');
if(!$conn){
	die("Error to connect database!");
}
$sql="select username from user where id=".$id;
echo "$sql"."</br></br>";
$res=$conn->query($sql);
#while($row=$res->fetch_row())  
if($res){
    $row=$res->fetch_row();
    echo "\t Hello <b>$row[0] </b>,Welcome to login,having a good day!";
    $res->free();
    $conn->close();
}else{
echo "<b>".mysqli_error($conn)."</b>";
}
?>

Download and install the latest v3.1.0-rc3 rules and enable blocking protection for testing.
I found a way to bypass the rules for SQL injection through black box testing.
This method is: {`a`b} , where a is a special function name, such as if, version, etc., and b is the sql statement to be executed.
Using the method to successfully bypass the rules for SQL injection, you can see that the database name was successfully read using the error.

http://127.0.0.1/test.php?id=1  and{`if`updatexml(1,concat(0x3a,(select /*!50000(/*!50000schema_name) from/*!50000information_schema*/.schemata limit 0,1)),1)}

In this way, you can use this method to bypass the crs rules and get any content in the database in the vulnerable system.

Please fix this security issue as soon as possible,Thank You.

As @lifeforms mentioned, the string information_schema can be found in different source files:

942140:information_schema\b
942190:from\W+information_schema\W
942330:\Winformation_schema

The problem in rule 942140 is, as @spartantri said, the word boundary: His solution with the (?:\w+)? works!
The problem with the rules 942190 and 942330 is the required non-word character in front of information_schema.
Rule 942190 could also be extended with the (?:\w+)?: from\W+(?:\w+)?information_schema\W.
Rule 942330 too: \W(?:\w+)?information_schema.

But that only solves the problem related to information_schema.

Either we extend all our sqli rules with this regexp or we make a new rule to detect extension annotation. I am not an sql pro (had a look at: https://dev.mysql.com/doc/refman/8.0/en/comments.html), but maybe something like that would work to detect (some) extensions:

\/\*[!+](?:[\w\s=_\-()]+)?\*\/

Tested with:
1 and{ifupdatexml(1,concat(0x3a,(select /*!50000(/*!50000schema_name) from/*!50000information_schema*/.schemata limit 0,1)),1)}
and from: https://dev.mysql.com/doc/refman/8.0/en/comments.html:
SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...
CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;
SELECT /*+ BKA(t1) */ FROM ... ;

I am not sure about false positives.
But the minimal string needed to trigger the regexp is: /*!*/ or /*+*/.
In my opinion this is not a very commonly used, legitimate string.

The rule 942440 from PL2 catches both /*! and */ independently. This is more prone to false positives.

I would rather write a new rule (maybe even on PL1) to catch these extensions, than to extend all sql rules.

Hi @seedis

thanks for sharing this! I've done some tests and I confirm that it works on PL1. By increasing the Paranoia Level (>=2) I can't exploit the SQLi anymore because of 942150 and 942100 rules.

I've changed your PHP script in order to make it works with the WordPress DB as follows:

<?php 

$id=$_GET['id'];
$conn=new mysqli('mysql','root','password','wordpress');

if(!$conn){
        die("Error to connect database!");
}

$sql="select user_login from wp_users where id=".$id;
echo "$sql"."</br></br>";
$res=$conn->query($sql);

if($res){
    $row=$res->fetch_row();

    echo '<pre>';
    print_r($row);
    echo '</pre>';
    
    echo "\t Hello <b>$row[0] </b>,Welcome to login,having a good day!";
    $res->free();
    $conn->close();
} else {
    echo "<b>".mysqli_error($conn)."</b>";
}

And used this payload (blind sqli, and something like user enumeration):

GET /test.php?id=9999%20or+{`if`(2=(select+2+from+wp_users+where+user_login='admin'))}

By using PL2 the same payload is blocked by rule 942150 SQL Injection Attack Matched Data: if found within MATCHED_VARS:ARGS:id: 9999 or {if(2=(select 2 from wp_users where user_login='admin'))}. Trying to bypass 942150 by changing if with something else, it is blocked by libinjection.

Probably, it could be intercepted by something like:

SecRule ARGS "@rx {\x60([^\x60]+)\x60" "id:999,\
  phase:1,\
  capture,\
  t:urlDecode,\
  t:removeWhitespace,\
  t:removeComments,\
  block,msg:'SQLi Bypass Attempt #1167',\
  logdata:'Detected using function %{tx.0}'"

With the same payload: 999 SQLi Bypass Attempt #1167 (Detected using function {`if`)

but I don't know if this could lead to many false positives.