问题描述
当我尝试使用包含撇号的值来执行PostgreSQL查询时，我被咬了第一个sql转义错误（很久以前）。 O'Brien ，使用FreePascal和Lazarus
I got bitten my first sql escaping error (it was long overdue) when I tried to execute the PostgreSQL query below with a value containing an apostrophe eg. O'Brien, using FreePascal and Lazarus
SQL.Add(format('select * from zones where upper(zn_name) >= %s and upper(zn_name) < %s order by zn_name',[sQuote(zoneMin), sQuote(zoneMax)]));
在上面的查询中，SQuote是一个用单引号包装字符串的函数。是否有一些标准的库用于清理Lazarus / FreePascal或Delphi的SQL查询参数？
In the query above SQuote is a function that wraps a string in single quotes. Is there some standard library for sanitizing SQL query parameters for Lazarus/FreePascal or Delphi for that matter?
推荐答案
您的应用程序容易受到一系列严重的安全问题，称为。请参阅。
Your application is vulnerable to a serious class of security problems called SQL injection. See http://bobby-tables.com/.
当然， O'Brian 导致错误，但是'）; DROP SCHEMA public; - ？或'）; DELETE FROM users; - ？第一个应用程序不应该工作，因为您的应用程序永远不会以超级用户或拥有表的用户身份运行，但是很少应用程序设计人员尽力实际执行此操作，并且经常在生产中运行特权用户。第二个将在大多数应用中工作;请参阅帖子的结尾以获取详细信息。
Sure, O'Brian causes an error, but what about ');DROP SCHEMA public;-- ? Or ');DELETE FROM users;-- ? The 1st shouldn't work because your app should never run as a superuser or the user that owns the tables, but few application designers make the effort to actually do that and often run privileged users in production. The 2nd will work in most applications; see the end of the post for details.
最简单和最好的预防措施是使用参数化语句 在您的客户端库中。有关Delpi，请参阅：
The easiest and best preventative measure is to use parameterized statements in your client library. See this example for Delpi:
To use a prepared statement, do something like this:
query.SQL.Text := 'update people set name=:Name where id=:ID';
query.Prepare;
query.ParamByName( 'Name' ).AsString := name;
query.ParamByName( 'ID' ).AsInteger := id;
query.ExecSQL;
（我从来没有使用过Delphi，最后在1995年写了Pascal代码;我只是引用
(I've never used Delphi and last wrote Pascal code in 1995; I'm just quoting the example given).
您目前正在做的是参数串。这是非常危险的。只有当您有引用SQL文字的强大功能时，才能安全地进行安装，而不仅仅是在每一端都引用引号，还可以处理其他转义，引用加倍等等。最后的办法
What you are doing currently is string interpolation of parameters. It is very dangerous. It can be done safely only if you have a robust function for quoting SQL literals, one that doesn't just bang quotes on each end, but also handles other escapes, quote doubling, etc. It is the approach of last resort; it's strongly preferable to use a parameterized statement.
这是我给出的例子的扩展以上。假设你是用用户名进行完美的普通插入，其中'Fred'是客户端输入的示例用户名：
Here's an expansion of the example I gave above. Say you're doing a perfectly ordinary insert of a user by username, where 'Fred' is an example username input by the client:
INSERT INTO users ( user_name ) VALUES ('Fred');
现在有些不愉快的人发送用户名'）; DELETE FROM users; - 。突然你的应用程序正在运行：
Now some unpleasant person sends the username ');DELETE FROM users;--. Suddenly your application is running:
INSERT INTO users ( user_name ) VALUES ('');DELETE FROM users;--');
展开时为：
INSERT INTO users ( user_name ) VALUES ('');
DELETE FROM users;
--');
或换句话说，插入一个空字符串的插入（虽然它们可以很容易地完美地有效的用户名），后跟一个 DELETE FROM users; 语句 - 删除用户中的所有行 - 然后注释什么也没做。 Splat。有数据。
or in other words an insert that inserts an empty string (though they could just as easily put a perfectly valid username in), followed by a DELETE FROM users; statement - deleting all rows in users - then a comment that does nothing. Splat. There goes your data.
参数化状态有时被错误地称为准备语句。这是不正确的，因为准备好的语句不一定是参数化的，参数化语句不一定准备好。混乱已经出现，因为许多语言的数据库接口没有提供一种使用参数化语句而不使用准备语句的方式。
Parameterized statemments are sometimes incorrectly referred to as prepared statements. That's incorrect because a prepared statement isn't necessarily parameterized, and a parameterized statement isn't necessarily prepared. The confusion has arisen because the database interfaces of many languages don't provide a way to use parameterized statements without also using prepared statements.
这篇关于对于FreePascal和Delphi，是否有一个用于消除PostgreSQL或SQL的查询参数的库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！