PHP SQL LIKE operator with percent sign wildcard cause SQL injection vulnerability.

One of my team member build a backend system and discovery the backend system can explore all the data by wildcard.

So let analysis and investigate why change UserID to percent sign can cause SQL injection and explore all data.

Part 0: The impact

User or hacker can view/explore all other user data by percent sign wildcard SQL injection on the URL input.

SQL query percent sign on LIKE query can be use to search all results.
Let’s understand how SQL Wildcards (%, _) are a threat.

% are special solely in the context of LIKE-matching. % equivalent to any string of zero or more characters.

An application is vulnerable to this attack when it uses the LIKE operator with a user received parameter not filtering any of these wildcard.

Part 1: Describe the vulnerable bug

So the Main reason cause the vulnerable is the PHP never filter or checking $GET['userid'] collect data on the URL , SQL query directly use the $GET['userid'] data. Hacker can change the data on the URL to make SQL injection attack.

LIKE operator with wildcard percent sign actually is not a vulnerable because % are special solely in the context of LIKE-matching for some use case .

The PHP code for GET userid from URL without checking escaped and validation or filter

Part 2: Steps to Reproduce

$UserID = (string)$_GET['userid'];

The SQL Query

 $sqlQuery= "SELECT * FROM userTable WHERE UserID LIKE '" . $UserID . "' ORDER BY UniqueID";

The normal userid on the URL

https://dev.domain.com/userInfo.php?action=view&userid=AYqNWDcPioJSa1Y3

sql query will only return the user info when the userid is AYqNWDcPioJSa1Y3

The SQL injection attack

https://dev.domain.com/userInfo.php?action=view&userid=%

sql query will return all user data

Part 3: Solution

We should never trust what user input.

1) Use Regular expression to verify and filter malicious code in input values and parameters.

2) Restrict input character format and check input length.

How to prevent SQL Injection

    $TheUserID = preg_replace('/[^A-Za-z0-9\-]/', '', (string)$_GET['data']);

    if(strlen($TheUserID) == 16)
    {
        $TheUserID = $UserID;
    }
    else
    {
        $TheUserID = "";
    }

First use regular expression search and remove all the special character.
And Validate userID since we know the userID characters length must be 16 character we can check the userID characters length if 16 character than process else will return empty userID since if the userID is length is more or less than 16 character length that mean must have something wrong.

Priority
P1

Severity
Critical: Loss of data.

Weakness
SQL Injection

Platform/Environment
All browser
Microsoft Azure PHP App Service (windows platform)
Microsoft Azure Database
Microsoft SQL Azure 12.0.2000.8

Language/Version
PHP 5.6.40 🐘
T-SQL (Transact-SQL) 12.0