Intro of SQL injection

This article is a recap of SQL Injection, including the purpose of this attack method and attack examples, as well as ways to prevent SQL Injection.

In addition, at this stage I focus on the combination of Java and SQL, so I will mention it a bit.

Introduction of SQL injection

SQL injection basically uses a vulnerability in SQL syntax to inpuesults on the server side.

Example

Before starting the example, let me add some knowledge here.

• Double quotes "" or single quotes '' are String in SQL
• Everything except above is SQL syntax
• The method of attack is to use this concept to decompose String into multiple query conditions to attack
• or other SQL syntax like — (annotation) etc.

Suppose there is a login form that looks like this

Of course, a normal password will not be stored directly like this. It will be encrypted before being stored in the database. I will write an article about this encryption process later, but this is a digression.

Attack steps

Before the attack, the original version of the SQL query looked like this. Only when both sides are true will it return true. Successful verification

select * from users where username = 'admin' AND password = 'password1';

1. First enter a single quote after admin

select * from users where username = 'admin'' AND password = 'password1';

When designing a form, if the message returned by error processing is not well written, a `syntax error` will appear. Because we know that the SQL syntax of this form to connect to the database is as follows, which means that the syntax is executed correctly, but the syntax content is wrong. , we can further trial and error to attack and below is the possible syntaxt.

2. Then enter OR ‘1’ = ‘1

select * from users where username = 'admin' OR '1' = '1' AND password = 'password1';

Always return true, verified successfully no matter what.

3. Other ways, to use — or # to turn syntax into annotations, like

Prevention

There are several precautions of the injection

• Create allowlist
• Use PreparedStatement like namedJdbc: pass values in parameters instead of directly manipulating SQL syntax

stat=conn
.prepareStatement(
  "insert into MyGuests (firstname, lastname, email) values ​​(?, ?, ?);"
);

• Do not use admin as a username

References

OWASP: https://owasp.org/www-community/attacks/SQL_Injection

YouTube: https://www.youtube.com/watch?v=2OPVViV-GQk

Cloudflare: https://www.cloudflare.com/zh-tw/learning/security/threats/how-to-prevent-sql-injection/