时间戳

首页 > 解决方案 > MYSQL 查询给出与 nodejs 中的输入值相同的输出

mysql - MYSQL 查询给出与 nodejs 中的输入值相同的输出

问题描述

我无法从 nodeJS 向 mysql 服务器发送安全 SQL 查询。

使用这段代码时：

        let test = "chart1yusd"

        db.query('SELECT '+ test +' FROM coinDetail WHERE id = ?',[
            requestID,
        ],function(err, result){
            console.log(result)
            res.send(result);
        });

我得到正确的输出：

[ RowDataPacket {
    chart1yusd:
     '[[1589846400000,0.118103090573034],[1590710400000,0.14990946026516133],[1591574400000,0.13832947332698067],[1592438400000,0.14626382998803866],[1593302400000,0.12312689681792738],[1594166400000,0.13064585929472963],[1595030400000,0.15134667446052835],[1595894400000,0.14511870584962466],[1596758400000,0.2044381065518002],[1597622400000,0.27718349745013865],[1598486400000,0.24733539468353966],[1599350400000,0.15428765583919232],[1600214400000,0.18333082011361068],[1601078400000,0.16554945200142196],[1601942400000,0.15536379703562367],[1602806400000,0.17817122591867382],[1603670400000,0.14901182983793498],[1604534400000,0.15243756831164262],[1605398400000,0.25106271236512906],[1606262400000,0.22676917209412703],[1607126400000,0.22559988488004115],[1607990400000,0.3198349358258863],[1608854400000,0.28175278764129286],[1609718400000,0.48270197854575086],[1610582400000,0.5562085890730089],[1611446400000,0.4835010798224596],[1612310400000,0.46142579899847125],[1613174400000,0.7327130188547617],[1614038400000,0.7803392989162374],[1614902400000,1.2216997730172996],[1615766400000,1.1508817751759253],[1616630400000,1.2024881678362118],[1617494400000,1.1159947150076852],[1618358400000,2.3093588705698713],[1619222400000,1.9654124655802336],[1620086400000,2.0674879115219373],[1621230497000,1.3424936470400413]]' } ]

但是当使用这段代码时（更安全的版本反对 sql 注入）：

    let test = "chart1yusd"

    db.query('SELECT ? FROM coinDetail WHERE id = ?',[
        test, requestID,
    ],function(err, result){
        console.log(result)
        res.send(result);
    });

我得到这个输出，而不是我以前想要的数据。

[ RowDataPacket { chart1yusd: 'chart1yusd' } ]

我做错了什么，我该如何解决这个问题？

标签： mysqlsqlnode.js

解决方案

The query SELECT ? FROM coinDetail WHERE id = ? is not a valid MySQL prepared statement, because only literal values can be represented by ? placeholders. Database objects, including column and table names, can't have placeholders.

So, you are basically stuck using your first version. If you know a priori that the chart1yusd column is what you want to select, then just hard code it:

SELECT chart1yusd FROM coinDetail WHERE id = ?

推荐阅读