Rails和SQL注入:这安全吗?

@usersfound = User.find_by_sql(["

SELECT * from users where name @@ plainto_tsquery('english', ?) LIMIT 20 offset ?

  ",@query,@offset])

See above, is this safe from sql injection? I am very new to doing direct sql commands on a database in rails. (I am aware there may be other ways of doing this SPECIFIC query, but I am wondering if in general, using find_by_sql and that kind of insertion of vars is safe - I have some difficult queries with subselects and joins that are really possible to do with ActiveRecord.

从上面看，这是安全的sql注入吗?我对在rails的数据库上执行直接的sql命令非常陌生。(我知道可能还有其他方法来执行这个特定的查询，但我想知道，一般来说，使用find_by_sql和那种插入vars是安全的——我有一些带有子选择和连接的困难查询，这与ActiveRecord是完全可能的。

Thanks.

谢谢。

2 个解决方案

#1

Yes, that should be safe. If you trace through the code you'll find that your find_by_sql call ends up calling PGconn#send_query_prepared with the bind parameters being carried along as little more than baggage; the send_query_prepared method is just a wrapper for the PQsendQueryPrepared API call in libpq:

是的，那应该是安全的。如果对代码进行跟踪，您将发现您的find_by_sql调用最终调用PGconn#send_query_prepared，绑定参数仅比行李多一点点;send_query_prepared方法只是libpq中PQsendQueryPrepared API调用的包装器:

static VALUE
pgconn_send_query_prepared(int argc, VALUE *argv, VALUE self)
{
    /* ... bunch of boiler plate marshalling stuff ... */
    result = PQsendQueryPrepared(conn, StringValuePtr(name), nParams, 
        (const char * const *)paramValues, paramLengths, paramFormats, 
        resultFormat);
    /* ... */
}

The bind parameters end up in paramValues. So you should be fine unless there are bugs in PostgreSQL's C library prepared statement handling.

绑定参数在参数中结束。所以，除非PostgreSQL的C库准备语句处理中有错误，否则应该没问题。

#2

Inserting dynamic values into a query using query parameters is safe.

使用查询参数将动态值插入查询是安全的。

But it depends on whether Rails is "faking" query parameters, and is actually combining @query and @offset into the SQL string before preparing the statement. Then it's only as safe as the implementation of escaping in Rails.

但这取决于Rails是否“伪造”查询参数，并且实际上是在准备语句之前将@query和@offset组合到SQL字符串中。那么只有在Rails中实现转义才是安全的。

#1

static VALUE
pgconn_send_query_prepared(int argc, VALUE *argv, VALUE self)
{
    /* ... bunch of boiler plate marshalling stuff ... */
    result = PQsendQueryPrepared(conn, StringValuePtr(name), nParams, 
        (const char * const *)paramValues, paramLengths, paramFormats, 
        resultFormat);
    /* ... */
}