My little SQL honey project (http://demo.greensql.net/) yield a problem in the existing implementation of GreenSQL SQL firewall. It turns out that SQL query located in whitelist can basically make the system ignore other query that has SQL comments inside. To make the things more clear I will give an example.

Let say I have the following SQL pattern in the whitelist:

“SELECT * from accounts where id = ?”

This query looks legitimate and it is indeed ok. Now, the system receives the following SQL command:

“SELECT * from accounts where id = ‘XXXX’ -- AND password = ‘YYYY’”

Hacker had injected SQL comment inside another legitimate query (“—“). In old version of GreenSQL, the resulting query pattern after SQL query normalization will be as followed:

“SELECT * from accounts where id = ?”

This is exactly as another SQL pattern in the whitelist. Because this pattern is approved, injected code will pass to the backend server. This will not generate any warnings.

In order to solve this problem, I decided to leave the comment symbol inside the SQL pattern and removing the comment itself. As a result, final SQL pattern after normalization will be as followed:

“SELECT * from accounts where id = ‘XXXX’ --”

This query pattern is different from the one located in whitelist. Now if GreenSQL administrator thinks that this pattern is legitimate, he will have to approve it explicitly.