node.js mysql LIKE语句用法

flvlnr44  于 2023-02-03  发布在  Node.js
关注(0)|答案(3)|浏览(218)
app.post('/like/:level/:name', function(req, res){
connection.query("SELECT * from books where " + req.params.level + " like '%" + req.params.name + "'%", function(err, rows, fields) {
if (!err){
var row = rows;
res.send(row);
console.log(req.params);
 console.log('The solution is: ', rows);}
else{
 console.log('Error while performing Query.');
console.log(err);}
});
});

根据上面的代码,有人能帮我找到我无法使用LIKE语句启动查询的原因吗?

the error is shown as
    { Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%' at line 1
    at Query.Sequence._packetToError (/root/Newfolder/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
    at Query.ErrorPacket (/root/Newfolder/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
    at Protocol._parsePacket (/root/Newfolder/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/root/Newfolder/node_modules/mysql/lib/protocol/Parser.js:75:12)
    at Protocol.write (/root/Newfolder/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/root/Newfolder/node_modules/mysql/lib/Connection.js:103:28)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    --------------------
    at Protocol._enqueue (/root/Newfolder/node_modules/mysql/lib/protocol/Protocol.js:141:48)
    at Connection.query (/root/Newfolder/node_modules/mysql/lib/Connection.js:208:25)
    at /root/Newfolder/trial.js:98:12
    at Layer.handle [as handle_request] (/root/Newfolder/node_modules/express/lib/router/layer.js:95:5)
    at next (/root/Newfolder/node_modules/express/lib/router/route.js:137:13)
    at Route.dispatch (/root/Newfolder/node_modules/express/lib/router/route.js:112:3)
    at Layer.handle [as handle_request] (/root/Newfolder/node_modules/express/lib/router/layer.js:95:5)
    at /root/Newfolder/node_modules/express/lib/router/index.js:281:22
    at param (/root/Newfolder/node_modules/express/lib/router/index.js:354:14)
    at param (/root/Newfolder/node_modules/express/lib/router/index.js:365:14)
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }
hrysbysz

hrysbysz1#

在查询字符串中,单引号出现在错误的位置。请更改此设置:

" like '%" + req.params.name + "'%"

......到这个:

" like '%" + req.params.name + "%'"

...这样第二个百分号就在单引号内。
如果您想执行注解中提到的"开始于"搜索,请从字段值的开头删除'%'

" like '" + req.params.name + "'%"

最后,不是你要问什么,而是你。

kfgdxczn

kfgdxczn2#

简单的方法:
SELECT * from books where ${req.params.level} LIKE '${req.params.name}%'
我觉得这里干净多了。

qnakjoqk

qnakjoqk3#

除了@nnnnnn的回答:如果像我这样的人遇到这个问题,想知道如何做@nnnnnn所做的事情,但使用参数来对SQL注入进行安全措施,下面是我的发现:

connection.query("SELECT * from books where ? like ?", [req.params.level, "%" + req.params.name + "%"] function(err, rows, fields) { ... }

这个查询对我来说工作得很好,并且使使用?-占位符和利用参数化成为可能:)

相关问题