如何编写参数化的SQL查询以防止SQL注入? [英] How to write parameterized sql query to prevent SQL injection?

问题描述

我最初发现这是一个问题，当我尝试搜索带有#标签的术语时，事实证明它是SQL中的注释定界符.搜索没有返回任何内容，因为它忽略了#标签后面的#term.

I initially discovered that this was an issue when I tried to search for terms that had been prepended with a hashtag, which it turns out is a comment delimiter in SQL. The search returned nothing, because it ignored the #term that came after the hashtag.

所以现在我很难找到转义用户输入的正确方法.在我看来，这既可以解决主题标签问题，又可以解决更大的问题，即SQL注入.

So now I'm having trouble finding the proper way of escaping the user's input. It seems to me that this would both solve the hashtag issue and also address the much larger problem, SQL injection.

以下是我正在使用的代码段:

Here is the snippet I am working with specifically:

function (term) {
  term = term.toLowerCase()
  return db('ticket')
    .select('*')
    .where(db.raw('lower(question)'), 'like', `%${term}%`)
    .orWhere(db.raw('lower(note)'), 'like', `%${term}%`)
    .orWhere(db.raw('lower(user_name)'), 'like', `%${term}%`)
}

我确实找到了此和

I did find this and this SO article that seemed close, as well as a couple other things. Also, Knex's docs and other sources recommend parameterized binding as a method to safeguard against SQL injection.

我只是很难找到一个清晰的示例，可以用JavaScript或使用Knex向我解释.

I'm just having trouble finding a clear example that can be explained to me in JavaScript or using Knex.

推荐答案

我不是Knex.js用户，但从文档看，似乎Knex使用JavaScript对象语法定义谓词就是它如何实现参数化.

I'm not a Knex.js user, but looking at the docs it seems that Knex's use of JavaScript object syntax to define predicates is how it achieves parameterization.

但是，当您使用内置功能时，您需要使用whereRaw.

However as you're using built-in functions you need to use whereRaw.

查看文档( http://knexjs.org/#Builder-whereRaw )， ( http://knexjs.org/#Raw-Bindings )我认为您想这样做:

Looking at the docs ( http://knexjs.org/#Builder-whereRaw ) and ( http://knexjs.org/#Raw-Bindings ) I think you want to do this:

.whereRaw('question LIKE :term OR note LIKE :term OR user_name LIKE :term', { term: '%' + term + '%' ] } )

Knex没有orWhereRaw，因此，如果要逻辑上分开谓词，则应使用简化版本:

Knex doesn't have an orWhereRaw, so you should use the longhand version if you want to logically separate the predicates:

term = '%' + term + '%';

.orWhere( knex.raw( 'question  LIKE ?', [ term ] ) )
.orWhere( knex.raw( 'note      LIKE ?', [ term ] ) )
.orWhere( knex.raw( 'user_name LIKE ?', [ term ] ) )

注意?用于位置参数，:term用于命名参数.

Note ? is for positional parameters, and :term is for named parameters.

这篇关于如何编写参数化的SQL查询以防止SQL注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！