Prevent SQL injection attacks in a Java program

Question

I have to add a statement to my java program to update a database table:

String insert =
    "INSERT INTO customer(name,address,email) VALUES('" + name + "','" + addre + "','" + email + "');";

I heard that this can be exploited through an SQL injection like:

DROP TABLE customer;

My program has a Java GUI and all name, address and email values are retrieved from Jtextfields. I want to know how the following code (DROP TABLE customer;) could be added to my insert statement by a hacker and how I can prevent this.

possible duplicate of [Java - escape string to prevent SQL injection](http://stackoverflow.com/questions/1812891/java-escape-string-to-prevent-sql-injection) — Nate, Mar 01 '12 at 13:03

score 26 · Accepted Answer · edited Feb 27 '14 at 08:04

26

You need to use PreparedStatement. e.g.

String insert = "INSERT INTO customer(name,address,email) VALUES(?, ?, ?);";
PreparedStatement ps = connection.prepareStatement(insert);
ps.setString(1, name);
ps.setString(2, addre);
ps.setString(3, email);

ResultSet rs = ps.executeQuery();

This will prevent injection attacks.

The way the hacker puts it in there is if the String you are inserting has come from input somewhere - e.g. an input field on a web page, or an input field on a form in an application or similar.

edited Feb 27 '14 at 08:04

answered Mar 01 '12 at 12:59

Matt Fellows

6,512
4
35
57

This will indeed prevent SQL injection attacks to a certain extent, because it separates the code from the data. Here's a short tutorial that covers [using Prepared Statements with Java JDBC.](https://www.youtube.com/watch?v=jTasm64rz-c) – drorw Nov 07 '18 at 10:13

m0skit0 · Answer 2 · 2012-03-01T13:06:37.033

15

I want to know how this kind piece of code("DROP TABLE customer;") can be added to my insert statement by a hacker

For example:

name = "'); DROP TABLE customer; --"

would yield this value into insert:

INSERT INTO customer(name,address,email)     VALUES(''); DROP TABLE customer; --"','"+addre+"','"+email+"');

I specially want to know how can I prevent this

Use prepared statements and SQL arguments (example "stolen" from Matt Fellows):

String insert = "INSERT INTO customer(name,address,email) VALUES(?, ?, ?);";
PreparedStament ps = connection.prepareStatment(insert);

Also parse the values you have on such variables and make sure they don't contain any non-allowed characters (such as ";" in a name).

edited Mar 01 '12 at 13:06

answered Mar 01 '12 at 13:01

m0skit0

25,268
11
79
127

I'm using a GUI. Person can only enter values through textfields. How can they add such piece of code to my statenment. like this INSERT INTO customer(name,address,email) VALUES(''); DROP TABLE customer; --"','"+addre+"','"+email+"'); – Grant Mar 01 '12 at 13:13
2

Why wouldn't you enter *'); DROP TABLE customer; --* in a text field? And anyway, a malicious user can hack those text fields limitations (e.g. modifying the RAM directly, injecting forged network packets, etc...). – m0skit0 Mar 01 '12 at 13:15
1

I didn't want to rewrite it myself :D – m0skit0 Mar 01 '12 at 13:33
1

@m0skit0 sorry for late reply. An ideal attack vector could be a restricted system, where the user won't have admin permissions or even way to escape to the main operating system. Having a textbox with SQL injection possibility and a keyboard input could allow possibly stealing credentials and other information stored in DB. How would you edit RAM in a coffee shop customer computer, and even if you could, couldn't you just get the auth credentials from program configuration and wreak havoc on server? – hegez Aug 22 '18 at 18:54

score 7 · Answer 3 · answered Mar 01 '12 at 12:58

You can check THIS article for info on that! :)

I recommend Parameterized Queries:

String selectStatement = "SELECT * FROM User WHERE userId = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, userId);
ResultSet rs = prepStmt.executeQuery();

score 5 · Answer 4 · answered Mar 01 '12 at 13:00

5

An attacker just has to enter something like 'foo@example.com"); DROP TABLE customer; into the field for email and you are done.

You can prevent this by using the proper escaping for JDBC Statements.

answered Mar 01 '12 at 13:00

Vlad Mihalcea · Answer 5 · 2018-01-05T17:37:20.587

As explained in this post, the PreparedStatement alone does not help you if you are still concatenating Strings.

For instance, one rogue attacker can still do the following:

call a sleep function so that all your database connections will be busy, therefore making your application unavailable
extracting sensitive data from the DB
bypassing the user authentication

And it's not just SQL, but JPQL and HQL can be compromised if you are not using bind parameters:

PreparedStatement ps = connection.prepareStatement(
    INSERT INTO customer(name,address,email) VALUES(?, ?, ?)
);
int index = 0;
ps.setString(++index, name);
ps.setString(++index, address);
ps.setString(++index, email);

ResultSet rs = ps.executeQuery();

Bottom line, you should never use string concatenation when building SQL statements. Use a dedicated API for that purpose:

score 2 · Answer 6 · answered Mar 01 '12 at 13:00

That's why you should be using question marks in your string statements:

 PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
                                     SET SALARY = ? WHERE ID = ?");
   pstmt.setBigDecimal(1, 153833.00)
   pstmt.setInt(2, 110592)

quoted from here

score 1 · Answer 7 · answered Nov 18 '15 at 11:32

Go for PreparedStatement Advantages of a PreparedStatement:

Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches.

Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters. Note that this requires that you use any of the PreparedStatement setXxx() methods to set the value

score 1 · Answer 8 · answered Feb 12 '20 at 00:17

You should also limit the privileges of the account that accesses the database as tightly as possible. For example, for searching, the account only needs to have read access to those tables and columns that are required. This will prevent any damaging SQL injection and limit access to sensitive data.

score 0 · Answer 9 · answered Nov 02 '18 at 08:20

Even though all the other answers tell you as how can you fix SQL injections in Java, answer by Mukesh Kumar actually tells you as who is actually preventing these kind of attacks. Understand that its actually DB server which is preventing SQL injection attacks provided you as a programmer follow their recommendation of using parametrized queries.

Refer Here - Preventing SQL Injection Vulnerabilities

It wouldn't be possible for Java programmer to sanitize each & every input String so DB vendors have given us options of Prepared Statements and they tell us to prepare & execute queries by using that & rest of the things will be taken care of by the DB vendor.

Things as drastic as DROP TABLE customer; might not happen but basic premise of SQL injection is that nobody should be able to break your query by just providing invalid input ( either intentional or non - intentional ).

OWASP - SQL Injection Prevention Cheat Sheet

Prevent SQL injection attacks in a Java program

9 Answers9

Linked

Related