我应该如何清理Java中的数据库输入？

Could someone please point me to a good beginner guide on safely running SQL queries formed partly from user input? I'm using Java, but a language neutral guide is fine too.

有人可以指点我一个关于安全运行部分来自用户输入的SQL查询的初学者指南吗？我正在使用Java，但语言中立的指南也很好。

The desired behaviour is that if someone types into the GUI something like

期望的行为是，如果有人在GUI中输入类似的东西

very nice;) DROP TABLE FOO;

非常好;）DROP TABLE FOO;

The database should treat it as a literal string and store it safely without dropping any tables.

数据库应将其视为文字字符串并安全地存储它而不删除任何表。

5 个解决方案

#1

You definitely want to use PreparedStatements. They are convenient. Here is an example.

你肯定想要使用PreparedStatements。他们很方便。这是一个例子。

#2

Use PreparedStatement instead of Statement

使用PreparedStatement而不是Statement

#3

Normally, you shouldn't create a query concatenating input, but using PreparedStatement instead.

通常，您不应创建连接输入的查询，而应使用PreparedStatement。

That lets you specify in which places you'll be setting your parameters inside your query, so Java will take care of sanitizing all inputs for you.

这允许您指定在查询中设置参数的位置，因此Java将负责为您清理所有输入。

#4

PreparedStatement? Yes, absolutely. But I think there's one more step: validation of input from UI and binding to objects prior to getting close to the database.

PreparedStatement的？是的，一点没错。但我认为还有一个步骤：在接近数据库之前验证来自UI的输入和绑定到对象。

I can see where binding a String in PreparedStatement might still leave you vulnerable to a SQL injection attack:

我可以看到在PreparedStatement中绑定String的位置可能仍然容易受到SQL注入攻击：

String userInput = "Bob; DELETE FROM FOO";
String query = "SELECT * FROM FOO WHERE NAME = ?";

PreparedStatement ps = connection.prepareStatement(query);
ps.setString(1, userInput);
ps.executeQuery();

I've gotta admit that I haven't tried it myself, but if this is remotely possible I'd say PreparedStatement is necessary but not sufficient. Validating and binding on the server side is key.

我必须承认我自己没有尝试过，但如果这是远远可能的话，我会说PreparedStatement是必要的，但还不够。在服务器端验证和绑定是关键。

I'd recommend doing it with Spring's binding API.

我建议使用Spring的绑定API。

#5

Your user input would actually have to be "Bob'; delete from foo; select '" (or something like that) so the implicit quotes added by the prepared statement would be closed:

您的用户输入实际上必须是“Bob”;从foo删除;选择'“（或类似的东西），因此预准备语句添加的隐式引号将被关闭：

SELECT * FROM FOO WHERE NAME = 'Bob'; delete from foo; select ''

but if you do that the prepared statement code will quote your quote so you get an actual query of

但如果您这样做，准备好的语句代码将引用您的引用，以便您获得实际查询

SELECT * FROM FOO WHERE NAME = 'Bob''; delete from foo; select '''

and your name would be stored as "Bob', delete from foo; select '" instead of running multiple queries.

并且您的名称将存储为“Bob”，从foo中删除;选择“”而不是运行多个查询。

#1