Understanding why this vulnerability exists

The reason this security risk exists is due to the user input directly to be inserted in addition to the SQL search performed to Database, this could cause unintended exposure of your data. The following diagram illustrates how malicious input flows from the client into the database without validation.

Real World Examples

Example 1: Search Endpoint

Intended use of search endpoint

GET /db?query='john'

This executes the following query:

SELECT * FROM users WHERE name = 'john';

Malicious or unintended use of search endpoint

GET /db/query' OR '1'='1

This executes the following query:

SELECT * FROM users WHERE name = '' OR '1'='1';

The result of this query will output every single value in the users table because 1 is always equal to 1.

What are the common methods to prevent SQL Injection?

Parameterized queries or prepared statements

Instead of replacing the users input directly into the query, we can treat the users input stricly as data. Through this way the inserted data will always be treated just as a string.

Use of prepared statements example:

SELECT * FROM users WHERE email = ?;

Even if we tried inserting something malicious the query actually executed would be somethings like:

SELECT * FROM users WHERE email = "'' OR '1'='1'";

Which would not return anything compromised because no email would be equal to "'' OR '1'='1'". This is the best method to prevent SQL Injections. Other methods like Input Validation are not as effective on detecting or controlling the malicious inputs.

How did we actually remediated this in the current solution?

In this exercise where we are presenting an unsecure and secure API, we tried where possible to rely on Kong's API Gateway as the source of our protection, in some vulnerabilities this was not possible and changes to the server side were needed. In this case for the SQL Injection we did not manage to fully cover our vulnerable SQL Injection vulnerabilities. The most effective solution was to perform changes in the server side. The following table can be used to cover what controls we have in place:

Available on Kongs Plugin we utilized:

• https://developer.konghq.com/plugins/injection-protection/ -> Validates users input with regex patterns, if it matches commonly used queries it blocks the attempt of a call

The two combine create a rule that only when the plugin detects a pattern in the malicious query string it will flag it as SQL Injection attempted and will not be processed by our server, but when it does cross over the barrier of the input validation we introduced prepared statements to fully protect the database.