在构建 SQL 查询和更新以提交到我的数据库之前,我需要清理一些用户输入的数据。
我知道最好使用准备好的陈述 https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_1:_Prepared_Statements_.28with_Parameterized_Queries.29但这不是一个选择。不幸的是,我被困住了转义所有用户提供的输入 https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_4:_Escaping_All_User_Supplied_Input.
看起来 Postgres JDBC 库附带了一个进行字符串转义的工具。看org.postgresql.core.Utils.escapeLiteral(..)
(见下文)。我希望由于 Postgres 附带了它,因此可以安全使用。经过几个小时的谷歌搜索和查看 SQL 备忘单后,我无法找到可以打破这个问题的示例。
以下看起来足够安全吗?
public class FruitDb {
private Connection connection;
public void findFruit ( String /* user enterable field */ fruitColor ) {
String query = "SELECT * FROM fruit WHERE fruit_color = " + quote( fruitColor );
Statement statement = connection.createStatement();
statement.executeQuery( sql );
}
private String quote( String toQuote ) {
return "'" + Utils.escapeLiteral( null, s, true ).toString() + "'";
}
}
对于那些对此感兴趣的人来说是实施Utils.escapeLiteral
。对我来说看起来相当安全......
package org.postgresql.core;
class Utils {
...
/**
* Escape the given literal <tt>value</tt> and append it to the string builder
* <tt>sbuf</tt>. If <tt>sbuf</tt> is <tt>null</tt>, a new StringBuilder will be
* returned. The argument <tt>standardConformingStrings</tt> defines whether the
* backend expects standard-conforming string literals or allows backslash
* escape sequences.
*
* @param sbuf the string builder to append to; or <tt>null</tt>
* @param value the string value
* @param standardConformingStrings if standard conforming strings should be used
* @return the sbuf argument; or a new string builder for sbuf == null
* @throws SQLException if the string contains a <tt>\0</tt> character
*/
public static StringBuilder escapeLiteral(StringBuilder sbuf, String value, boolean standardConformingStrings)
throws SQLException
{
if (sbuf == null)
{
sbuf = new StringBuilder(value.length() * 11 / 10); // Add 10% for escaping.
}
doAppendEscapedLiteral(sbuf, value, standardConformingStrings);
return sbuf;
}
private static void doAppendEscapedLiteral(Appendable sbuf, String value, boolean standardConformingStrings)
throws SQLException
{
try
{
if (standardConformingStrings)
{
// With standard_conforming_strings on, escape only single-quotes.
for (int i = 0; i < value.length(); ++i)
{
char ch = value.charAt(i);
if (ch == '\0')
throw new PSQLException(GT.tr("Zero bytes may not occur in string parameters."), PSQLState.INVALID_PARAMETER_VALUE);
if (ch == '\'')
sbuf.append('\'');
sbuf.append(ch);
}
}
else
{
// REMOVED. I am using standard encoding.
}
}
catch (IOException e)
{
throw new PSQLException(GT.tr("No IOException expected from StringBuffer or StringBuilder"), PSQLState.UNEXPECTED_ERROR, e);
}
}
}
类似问题:
-
如何使用 Java 在 PostgreSQL 中安全地转义 SQL 的任意字符串 https://stackoverflow.com/questions/9741206/how-to-safely-escape-arbitrary-strings-for-sql-in-postgresql-using-java/43645849#43645849 - 我实际上回答了这个建议使用 Utils.escapeLiteral(..)因为我认为这是一个比预期答案更好的解决方案。
- 我可以通过转义单引号并用单引号包围用户输入来防止 SQL 注入吗? https://stackoverflow.com/questions/139199/can-i-protect-against-sql-injection-by-escaping-single-quote-and-surrounding-use
-
非常好的帖子: SQL Server 中的 SQL 注入如何击败转义单引号的卫生措施? https://stackoverflow.com/questions/15537368/how-can-sanitation-that-escapes-single-quotes-be-defeated-by-sql-injection-in-sq