防止Java程序中的SQL注入攻击

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

我必须在我的java程序中添加一个语句来更新数据库表：

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

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

我听说这可以通过SQL注入来利用，如：

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.

我的程序有一个Java GUI，所有的名称，地址和电子邮件值都是从Jtextfields中检索的。我想知道以下代码（DROP TABLE customer;）如何被黑客添加到我的插入语句中以及如何防止这种情况。

7 个解决方案

#1

You need to use PreparedStatement. e.g.

您需要使用PreparedStatement。例如

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.

黑客把它放在那里的方式是你插入的字符串是否来自某处的输入 - 例如网页上的输入字段，或应用程序中类似的表单上的输入字段。

#2

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

我想知道黑客如何将这种代码（“DROP TABLE customer;”）添加到我的插入语句中

For example:

例如：

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

would yield this value into insert:

会将此值转换为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):

使用预处理语句和SQL参数（例如来自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).

还要解析对这些变量的值，并确保它们不包含任何不允许的字符（例如名称中的“;”）。

#3

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

#4

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

攻击者只需输入类似'foo@example.com“的内容; DROP TABLE customer;进入电子邮件领域即可完成。

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

您可以通过使用适当的JDBC语句转义来防止这种情况。

#5

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

引自这里

#6

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

正如本文中所解释的那样，如果您仍在连接字符串，那么PreparedStatement本身并没有帮助您。

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
调用sleep函数，以便所有数据库连接都忙，从而使您的应用程序不可用
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:

并且它不仅仅是SQL，但如果您不使用绑定参数，则可能会损害JPQL和HQL：

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:

最重要的是，在构建SQL语句时，不应该使用字符串连接。为此目的使用专用API：

JPA Criteria API
JPA Criteria API
jOOQ
jOOQ

#7

Go for PreparedStatement Advantages of a PreparedStatement:

转到PreparedStatement 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.

SQL语句的预编译和数据库端缓存可以提高整体执行速度，并能够批量重用相同的SQL语句。

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

通过内置转义引号和其他特殊字符自动防止SQL注入攻击。请注意，这要求您使用任何PreparedStatement setXxx（）方法来设置值

#1