Sql Injection

1

1. What is SQL Injection?

SQL injection refers to the act of someone inserting a SQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a SQL statement that you will unknowingly run on your database.

2. SQL Injection Example.

Below is a sample string that has been gathered from a normal user and a bad user trying to use SQL Injection. We asked the users for their login, which will be used to run a SELECT statement to get their information.

// a good user's name

name = "Ravi";

query = "SELECT * FROM customers WHERE username = 'name'";

Logger.debug(“This is normal Entry from the user-----------à”);

// user input that uses SQL Injection

name_bad = "' OR 1'";

// our SQL query builder, however, not a very safe one

query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";

// display what the new query will look like, with injection

Logger.debug(“This is bad input from user trying to inject SQL Vulnerability-----à”);

3. Query Formation for both Inputs.

Normal Query: SELECT * FROM customers WHERE username = ‘Ravi’
Injection Query: SELECT * FROM customers WHERE username = '' OR 1''

The normal query is no problem, as our SQL statement will just select everything from customers that has a username equal to Ravi.

However, the injection attack has actually made our query behave differently than we intended. By using a single quote (') they have ended the string part of our SQL query

• username = ' '

and then added on to our WHERE statement with an OR clause of 1 (always true).

• username = ' ' OR 1

This OR clause of 1 will always be true and so every single entry in the "customers" table would be selected by this statement

Although the above example displayed a situation where an attacker could possibly get access to a lot of information they shouldn't have, the attacks can be a lot worse. For example an attacker could empty out a table by executing a DELETE statement.

name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 

// our MySQL query builder really should check for injection

query_evil = "SELECT * FROM customers WHERE username = 'name_evil'";

// the new evil injection query would include a DELETE statement

Logger.debug(“This will delete everything from the database table customers------à”);

Display:

SELECT * FROM customers WHERE username = ' '; DELETE FROM customers WHERE 1 or username = ' '

If you were run this query,then the injected DELETE statement would completely empty your "customers" table. Now that you know this is a problem, how can you prevent it?

4. Dynamic Queries via String Concatenation

The important thing to remember is to never construct SQL statements using string concatenation of unchecked input values. Creating of dynamic queries via the java.sql.Statement class leads to SQL Injection.

§ SQL Injection Prevention

All queries should be parametrized.

All dynamic data should be explicitly bound to parametrized queries.

String concatenation should never be used to create dynamic SQL.