sql 注入 - 如何清理程序生成的 sql 子句? [英] sql injection - how to sanitize program generated sql clause?

问题描述

在标准 Ajax 中，where 和 order by SQL 子句由程序(不是用户)提供，例如

var url = ".select?dd=emp&where="+escape("emp_tp='abc' andhiring_dt

在服务器上回答

$where = (isset($_GET['where'])) ?pureClause($_GET['where']) : null;$order = (isset($_GET['order'])) ?pureClause($_GET['order']) : null;...$query = $query.(($where)?" where $where":'').(($order)?" order by $order":'');

问题是pureClause函数应该是什么样子的?

现在 pureClause 如果存在以下任何一项，则只会引发错误:

<代码>;选择插入更新删除删除创建截断

如果其他注入导致查询失败，没关系，只要数据未损坏.

对我来说这似乎足够了，但在我心里，我知道我错了.

说明:

• 在 Postgres 中准备好的语句虽然速度非常快，但设置和维护起来很麻烦 - 它们适用于使用良好的查询，但不适用于自定义查询.
• 为每个事务创建一个准备好的语句是一个巨大的 db 命中.如果可以在应用级别实现安全性，则更可取.

最后，考虑 where 子句

emp_tp='abc' andhiring_dt=current_dt-'2 years' and super_emp_id 与 emp_id 不同

这里有多少个占位符?这需要在被输入带有占位符的准备好的语句之前正确解析，对吗?还是我完全错过了这条船?

---

主要事实:

• 不为参数化准备语句编写 SQL 子句解析器不实用
• 不写一个保证无害的 SQL 子句清理器是不切实际的

解决方案:

对于 SELECTS，随机 SQL 可能是一个问题:既然保护数据库太难了，就让数据库保护自己吧！有不同的用户有不同的角色/权限.使用只读用户进行选择.对于普通 SQL，这保证了这些语句中没有 DML.

最佳实践:四个数据库用户访问

1. developer，做任何事情(从不在网络应用程序中用作连接)
2. dml - 可以在几乎所有东西上选择/dml(必须用于 dml)
3. read - 可以选择(用于所有选择，无论是准备好的还是文本)
4. login - 只能执行登录/密码功能(用于登录过程)

密码保护:

• dml 和 read 可能无法通过 select 或 dml 访问密码数据
• login 应该仅通过受保护的功能访问密码数据，例如，

<预>函数登录(用户名，密码) - 返回 user_id函数 set_password( usr_id, password ) - 设置密码

• 只有 login 可以运行 login() 和 set_password() 函数
• 根据您的数据库，login 可能需要 sql 访问密码列
• 根据您的数据库，password 列可能会受到保护；如果没有，那么应该从 user 表中移出到它自己的安全表中

在mysql中进行设置，使用管理员工具，大约需要30分钟，包括编写登录函数和拆分密码列的时间.

解决方案

明白了！通过仅被授予数据库 SELECT 权限的数据库用户(连接)路由所有这些查询！

尝试 DML 会窒息.这并不能阻止 DoS 攻击(有很多方法可以做到！)，但可以保护数据.也不会进行安全查询，例如登录.但是对于客户端生成的 WHERE 和 ORDER，为了防止 DML，这应该可以正常工作.

十年/十五年前总是为不同的角色设置不同的用户，但是应用层等已经不习惯了.重新投资于这些原则可能是个好主意.

除非听到不同的声音会将其标记为正确答案 - 它满足所有标准，但它避免了编写消毒剂理论上不可能的挑战.

in standard Ajax, where and order by SQL clauses are provided by the program (not user), eg

var url = ".select?dd=emp&where="+escape("emp_tp='abc' and hire_dt<current_date-'2 years' and super_emp_id is distinct from emp_id")

answered on the server by

$where = (isset($_GET['where'])) ? pureClause($_GET['where']) : null;
$order = (isset($_GET['order'])) ? pureClause($_GET['order']) : null;
...
$query = $query.(($where)?" where $where":'').(($order)?" order by $order":'');

the question is what should function pureClause look like?

right now pureClause simply raises error if any of the following exist:

; select insert update delete drop create truncate

if other injection causes query failure, that's fine, as long as data undamaged.

to me this seems adequate, but in my heart, I know I'm wrong.

Clarifications:

• prepared statements in Postgres, although very fast, are a pain to set up and maintain - they're ok for well used queries but not custom queries.
• creating a prepared statement for each transaction is a huge db hit. much preferred if security can be attained in at the app level.

Lastly, consider the where clause

emp_tp='abc' and hire_dt=current_dt-'2 years' and super_emp_id is distinct from emp_id

how many placeholders here? this needs to be parsed correctly before being fed into a prepared statement with placeholders, right? or am I completely missing the boat?

---

Primary facts:

• not practical to write a SQL clause parser for parameterized prepared statements
• not practical to write a SQL clause sanitizer that guarantees no harm

Solution:

for SELECTS, where the random SQL can be a problem: since it's too hard to protect the database, let the database protect itself! have different users have different roles / permissions. use a read-only user for selects. for normal SQL, this guarantees no DML from these statements.

best practices: four db user accesses

1. developer, do everything (never use as connection in web app)
2. dml - can select / dml on almost everything (must use for dml)
3. read - can select (use for all selects, whether prepared or text)
4. login - can only execute login/password functions (used in login process)

password protection:

• dml and read may not access password data, either through select or dml
• login should access password data only through protected functions, eg,

     function login( username, password ) - returns user_id
     function set_password( usr_id, password ) - sets password

• only login may run the login() and set_password() functions
• depending on your database, login may need sql access to password columns
• depending on your database, the password column may be protected itself; if not, then should be moved out of the user table into its own secure table

setting this up in mysql, using the administrator tool, took about 30 minutes, including time to write the login functions and split out the password column.

解决方案

Got it! Routing all of these queries through a database user (connection) who has only been granted SELECT privileges on the database!

Attempted DML will choke. This does not prevent DoS attacks (lots of ways to do that!), but does protect the data. Nor does the make for secure queries, like login. But for client generated WHERE and ORDER, with the goal of preventing DML, this should work just fine.

Ten/fifteen years ago always set up different users for different roles, but with app layer etc etc got out of the habit. It's probably a good idea to re-invest in those principles.

Unless hear differently will mark this as correct answer - it satisfies all criteria, howbeit it dodges the theoretically impossible challenge fo writing a sanitizer.

这篇关于sql 注入 - 如何清理程序生成的 sql 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！