将变量插入节点mysql的SQL查询中 [英] Inserting variables into SQL query for node-mysql

问题描述

在用于node-mysql的Wiki中，我注意到以两种不同的方式将变量(种类)插入SQL查询中，一种是通过在查询字符串的中间插入变量，另一种方式是通过将?放在查询字符串中，并在下一个参数的数组中定义这些?的值.查询中替换?的值被转义.

In the wiki for node-mysql, I noticed that variables (kind of) were inserted into the SQL query in 2 different ways, one is by inserting a variable in the middle of the query string, and the other way is by putting ? in the query string and defining the values for these ? in an array in the next argument. The values replacing ? in the query are escaped.

有什么区别?什么时候应该使用另一种? (如果我们希望表名是一个变量，是否应该将其转义?)

What is the difference and when should I use one over the other? (If we want the table name to be a variable, should it be escaped?)

Wiki代码

client.query(
    'INSERT INTO '+TEST_TABLE+' '+
    'SET title = ?, text = ?, created = ?',
    ['super cool', 'this is a nice text', '2010-08-16 10:00:23']
);

Wiki链接: https://github.com/felixge/node- mysql

Wiki摘录:

将sql查询发送到服务器. '?'字符可以用作参数数组的占位符，这些参数将在发送最终查询之前安全地转义.

Sends a sql query to the server. '?' characters can be used as placeholders for an array of params that will be safely escaped before sending the final query.

此方法返回一个Query对象，该对象可用于流式处理传入的行数据.

This method returns a Query object which can be used to stream incoming row data.

推荐答案

始终支持选项2，因为它们是SQL注入证明.选项1将在5分钟内使您的网站被黑客入侵，而黑客将很难突破选项2.

Always favor option 2, since they are SQL injection proof. Option 1 will get your site hacked within 5 minutes, while hackers are going to have a hard time breaking option 2.

即使在性能选项2中，速度可能也会快一点.

Even in performance option 2 may be a little bit faster.

但是:不能使用?来转义表名，所以不要去那里.只要确保用户不能手动输入表名，您就可以免受黑客攻击.

However: Table names cannot be escaped with the ? so don't go there. Just make sure that users can't enter the table name manually and you'll be safe from hackers.

-编辑-

为什么仍然要使表名可变?

Why would you want to make table names variable anyway?

这篇关于将变量插入节点mysql的SQL查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！