使用 org.postgresql.core.Utils.escapeLiteral 是否足以防止 SQL 注入? [英] Is using org.postgresql.core.Utils.escapeLiteral enough to prevent SQL Injections?
问题描述
在构建 sql 查询和更新以提交到我的数据库之前，我需要清理一些用户输入的数据.
I need to sanitize some user entered data before building sql queries and updates to submit to my DB.
我知道最好使用 准备好的语句 但这不是一个选项.不幸的是，我坚持使用转义所有用户提供的输入.
I know that it is preferable to use either prepared statements but this is not an option. Unfortunatly, I am stuck with escaping all user supplied Input.
看起来 Postgres JDBC 库带有一个工具来进行字符串转义.参见 org.postgresql.core.Utils.escapeLiteral(..)(附在下面).我希望因为 Postgres 附带了它，所以使用起来是安全的.经过几个小时的谷歌搜索和查看 SQL 备忘单后，我无法找到一个可以打破这个的例子.
It looks like the Postgres JDBC libs come with a tool to do String escaping. See org.postgresql.core.Utils.escapeLiteral(..) (attached below). I am hoping that since this comes with Postgres, that it is safe to use. After several hours of googling and looking at SQL cheatsheets I am unable to find an example that will break this.
以下看起来足够安全吗?
Does the following look safe enough?
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 的实现.对我来说看起来相当安全...
For those interested here is the implementation of Utils.escapeLiteral. Looks reasonably safe to me...
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);
}
}
}
<小时>
类似问题:
Similar Questions:
- 如何使用 Java 在 PostgreSQL 中安全地转义 SQL 的任意字符串 - 我实际上回答了这个建议使用 Utils.escapeLiteral(..) 因为我认为这是一个比例外答案更好的解决方案.
- 我可以保护吗通过转义单引号和用单引号包围用户输入来对抗 SQL 注入?
- 非常好的帖子: 转义单引号的卫生如何在 SQL Server 中被 SQL 注入打败?
- How to safely escape arbitrary strings for SQL in PostgreSQL using Java - I actually answered this suggesting to use Utils.escapeLiteral(..) because I think that is a better solution than the excepted answer.
- Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?
- Very good post: How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?
推荐答案
是的，使用escapeLiteral() 和escapeIdentifier() 是安全的.
Yes, it is safe to use escapeLiteral() and escapeIdentifier().
实际上，escapeLiteral() 和escapeIdentifier() 是libpq 中定义的PQescapeLiteral() 和PQescapeIdentifier() 的补充.
In fact, escapeLiteral() and escapeIdentifier() are complements for PQescapeLiteral() and PQescapeIdentifier() that are defined in libpq.
主要区别在于 JDBC 的 escapeLiteral() 不考虑数据库连接来验证字符编码.但是，Java 的内部编码是 Unicode 并转换为数据库字符编码.因此，没关系.
The main difference is JDBC's escapeLiteral() doesn't consider database connection to validate character encoding. However, Java's internal encoding is Unicode and converted to database character encoding. Therefore, it does not matter.
我注意到的另一个区别是如何将字符串转义为 SQL 文字.PQescapeLiteral 为字符串添加引号，但 escapeLiteral() 没有.例如abc'xyz 用 PQescapeLiteral() 变成了 'abc''xyz'，但它变成了 abc''xyz (注意结果周围没有引号)
Another difference I noticed is how strings are escaped as SQL Literal. PQescapeLiteral adds quotes for string, but escapeLiteral() doesn't. e.g. abc'xyz became 'abc''xyz' with PQescapeLiteral(), but it became abc''xyz (Notice no quotes around the result)
escapeIdentifier() 添加引号就像 PQescapeIdentifier() 一样.例如abc"xyz 变成了 "abc""xyz"
escapeIdentifier() adds quotes just like PQescapeIdentifier(). e.g. abc"xyz became "abc""xyz"
CERT TOP 10 安全编码实践 #7(清理输出)建议清理所有变量，而不管之前的检查/验证如何.通过使用准备好的查询可以避免使用escapeLiteral()，但不能使用escapeIdentifier()，因为准备好的查询不能分离参数化标识符.即
CERT TOP 10 Secure Coding Practices #7 (Sanitize Output) suggests to sanitize all variables regardless of previous checks/validations. One can avoid using escapeLiteral() by using prepared query, but not escapeIdentifier() since prepared query cannot separate parameterized identifiers. i.e.
SELECT user_specified_col FROM tbl;
或
SELECT * FROM tbl ORDER BY user_specified_col;
开发人员必须验证user_specified_col"；严格来说，但他们必须清理参数以防验证不当.即输出清理必须独立完成.
Developers must validate "user_specified_col" strictly, but they must sanitize parameters in case for improper validation. i.e. Output sanitization must be done independently.
这篇关于使用 org.postgresql.core.Utils.escapeLiteral 是否足以防止 SQL 注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
