SQLi Attacks and Defence

1. Returning more data than expected

Envision a developer needs to demonstrate the record numbers and equalization for the present client's id as gave in a URL. They may compose (in Java):

Under normal operation, the user with ID 984 might be logged in, and visit the URL:

https://bankingwebsite/show_balances?user_id=984

This means that accountBalanceQuery would end up being:

This is passed to the database, and the accounts and balances for user 984 are returned, and rows are added to the page to show them.

The attacker could change the parameter “user_id” to be interpreted as:

And this results in accountBalanceQuery being:

When this query is passed to the database, it will return all the account numbers and balances it has stored, and rows are added to the page to show them. The attacker now knows every user’s account numbers and balances.

Repair

The developer could easily repair this vulnerability by using a prepared statement to create a parameterized query as below:

If an attacker attempts to supply a value that’s not a simple integer, then statement.setInt() will throw a SQLException error rather than permitting the query to complete.

2. Making your user into an administrator

Imagine a developer implements a login form, and writes (in Java):

Normally, a user would provide a username (say “john”) and password, and the first query executed would be:

The database returns John’s user ID and password hash, John’s password is hashed compared, and then the database gets the list of groups to which John belongs.

However, if an attacker has John’s password (maybe the attacker is John!), they could make John into an admin. They’d login using the following as a username (newlines added for ease of reading):