SQL injection

Figure 858. Assembling SQL Slide presentation Create comment in forum

Figure 859. SQL injection principle Slide presentation Create comment in forum

Figure 860. Preventing traffic tickets Slide presentation Create comment in forum
Preventing traffic tickets

Figure 861. Trouble at school Slide presentation Create comment in forum
Trouble at school

Before diving into technical details we shed some light on the possible impact of this common attack type:

Figure 862. SQL injection impact Slide presentation Create comment in forum
SQL injection impact

Figure 863. SQL injection relevance, [Clarke2009] Slide presentation Create comment in forum

Many people say they know what SQL injection is, but all they have heard about or experienced are trivial examples.

SQL injection is one of the most devastating vulnerabilities to impact a business, as it can lead to exposure of all of the sensitive information stored in an application's database, including handy information such as usernames, passwords, names, addresses, phone numbers, and credit card details.


Figure 864. Lessons learned? Slide presentation Create comment in forum

Current achievements continue to be questionable.


exercise No. 29

Attack from the dark side Create comment in forum

Q:

Use your Interactive inserts, connection properties, error handling and unit tests application and the idea of Figure 859, “SQL injection principle ” to launch an SQL injection attack. We provide some hints:

  1. The Mysql JDBC™ driver implementation already provides precautions to hamper SQL injection attacks. In its default configuration a sequence of SQL commands separated by semicolons (;) will not be executed but flagged as a SQL syntax error. We take an example:

    INSERT INTO Person VALUES (...);DROP TABLE Person

    In order to execute these so called multi user queries we explicitly have to enable a Mysql property thereby overriding the default security configuration:

    jdbc:mysql://localhost:3306/hdm?useSSL=false&allowMultiQueries=true

    The Mysql manual contains a remark regarding this parameter:

    Notice that this has the potential for SQL injection if using plain java.sql.Statements and your code doesn't sanitize input correctly.

    In other words: You have been warned!

  2. You may now use either of the two input fields name or email to inject arbitrary SQL code.

A:

Logging tells us about SQL code being generated when inserting a record based on e.g. user Eve having an email eve@my.org:

main INFO  insert.SimpleInsert - Executing «INSERT INTO Person VALUES('Eve', 'eve@my.org')»

We craft our first input username replacing Eve to launch our attack:

Eve', 'eve@my.org');DROP TABLE Person;INSERT INTO Person VALUES('jim

A corresponding dialog reads:

MinimumTest> java -jar /ma/goik/GoikLectures/P/Sda1/Jdbc/Insert/MinimumTest/target/insert_user-0.1.jar
Enter a person's name or 'x' to exit: Eve', 'eve@my.org');DROP TABLE Person;INSERT INTO Person VALUES('jim
Enter Eve', 'eve@my.org');DROP TABLE Person;INSERT INTO Person VALUES('jim's email or 'x' to exit: jim@company.com

This successfully kills our Person table:

goik@goikschlepptop MinimumTest> cat A1.log
main INFO  insert.SimpleInsert - Executing «INSERT INTO Person VALUES('Eve', 'eve@my.org');DROP TABLE Person;INSERT INTO Person VALUES('jim', 'jim@company.com')»
main ERROR insert.SimpleInsert - General database connection problem:
java.sql.SQLSyntaxErrorException: Table 'hdm.Person' doesn't exist
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112) ~[insert_user-0.1.jar:?]
...

According to the message text the table Person gets dropped as expected. Thus the subsequent (second) INSERT action is then bound to fail.

In practice this result may be avoided: The database user in question will (hopefully!) not have sufficient permissions to drop the whole table. Use GRANT / REVOKE statements accordingly!

Malicious modifications by INSERT, UPDATE or DELETE statements of data records are still possible though!