CODEWITHSUNDEEP
javamysqlsqlsql-injection
Grant
Grant

Reputation: 4543

Prevent SQL injection attacks in a Java program

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.

Upvotes: 15

Views: 45114

Answers (9)

Jason210
Jason210

Reputation: 3350

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.

Upvotes: 1

Sabir Khan
Sabir Khan

Reputation: 10132

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

Upvotes: 0

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153730

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:

Upvotes: 2

Mukesh Kumar
Mukesh Kumar

Reputation: 327

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

Upvotes: 1

Matt Fellows
Matt Fellows

Reputation: 6522

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.

Upvotes: 26

m0skit0
m0skit0

Reputation: 25873

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).

Upvotes: 15

dimitrisli
dimitrisli

Reputation: 21391

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

Upvotes: 2

user647772
user647772

Reputation:

An attacker just has to enter something like '[email protected]"); DROP TABLE customer; into the field for email and you are done.

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

Upvotes: 5

aF.
aF.

Reputation: 66697

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();

Upvotes: 7

Related Questions