Node-mysql2: execute vs query

[afwn90cj93201nixr2e1re]

.

[afwn90cj93201nixr2e1re]

https://github.com/sidorares/node-mysql2/issues/518
i have read it.

But also i have a question.
Execute - no possibly sql injection
query - possibly sql injection
Right?

@sidorares

[afwn90cj93201nixr2e1re]

Can you show real example with some sql injection.
Because i realy can't understand difference beetween prepared statement with query and with execute.

https://github.com/mysqljs/mysql#escaping-query-values

var userId = '1;\'DROP TABLE test;';
var columns = 'id';
var query = pool.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'test', userId], function (error, results, fields) {
    if (error) throw error;
    console.log(`results = `,results);
    console.log(query.sql);
});

results =  [ TextRow { id: 1 } ]
SELECT `id` FROM `test` WHERE id = '1;DROP TABLE test;'



md5-bd59e17b737781122200e651064b721e



results =  [ TextRow { id: 1 } ]
SELECT `id` FROM `test` WHERE id = '1;\'DROP TABLE test;'



md5-4ebc128df5a4bc1fa791b285f1980902



// var userId = '1';
// userId = userId +';';
// userId = userId +"'";
// userId = userId +"DROP TABLE test; --";
var userId = `1;'DROP TABLE test; --`
var columns = 'id';
var query = pool.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'test', userId], function (error, results, fields) {
    if (error) throw error;
    console.log(`results = `,results);
    console.log(query.sql);
});



md5-2ac0656d782962ef313ab0ad2ff02d78



results =  [ TextRow { id: 1 } ]
SELECT `id` FROM `test` WHERE id = '1;\'DROP TABLE test; --'

also gonna escape

where is the diffs with execute?)

or execute is just step by step generating query on mysql server (but i can't get for what it must be used)
and query - is ready query which sending into mysql server cmd, then execute and return result?

[sidorares]

I'll give you JS analogy:

Say you want to execute some javascript function ( for example, add two numbers, a user input ).

Non-prepared statements version:

// clilent
const arg1 = escape(userInput1);
const arg2 = escape(userInput2);
const code = arg1 + "+" + arg2;

// server
const result = eval(code);
return result;

Prepared statements version:

// client:
const code = 'function (arg1, arg2) { return arg1 + arg2 }';
// sends code to prepare to server

// server
const stmtId = 1;
statements[stmtId] = eval(code);
// send stmtId  to client

// client again:
// hey server, please execute arg1 + arg2  ( sends arg1 and arg2 unescaped )

//server:
const result = statements[stmtId](arg1, arg2);

[sidorares]

Execute - no possibly sql injection
query - possibly sql injection

Right ( but only if all user data comes as PS parameter and never as part of a statement sql itself )
.query() parameter escaping is very robust as well and there is no known sql injections vulnerabilities known right now ( here are some example where bugs in libmysql client-side escaping has bitten in the past: https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string/12118602#12118602 )

[afwn90cj93201nixr2e1re]

So, ok. Is that stored procedures analog?
As i get,
execute provide args as string/int/other types, and mysql engine accept it and ignore this args as commands.

So, then, can you provide some examples with best practices for execute cache cleaning?
Or mysql2 has auto clear execute prepared statement?
Can you exaplain clearing strategy?
Is prepared statements gonna save on mysql server or in node.js?

Can you show real example? Like make it works.

const pool = mysql2.createPool({host:'localhost', user: 'admin', database: 'test',password:'admin'});

async function CheckLoginAndPassword(req,res,next){
    try{
        const  [rows, fields] = await pool.execute('SELECT * FROM users WHERE login = ?', [req.body.login]);
        if(rows && fields){//is it right check?
                 console.log(rows);
                 console.log(fields);
        }
        else{return res.status(401).send({"error":{"message": 'Can\'t find it'}});}
    }
    catch(err){
        console.error(err.message);
        // return res.status(401).send({"error":{"message": autherr}});
    }
}

[afwn90cj93201nixr2e1re]

const mysql2 = require('mysql2');
const pool = mysql2.createPool({host:'localhost', user: 'admin', database: 'test',password:'password'});
const promisePool = pool.promise();//is that normal?
async function CheckLoginAndPassword(/*req,res,next*/){
    try{
        let login = 'abcd';
        const someobj = await promisePool.execute('SELECT * FROM users WHERE login = ?', [login]);
        if(someobj.rows){//but this also true if array has no element
              console.log(someobj.rows);
        }
        else{ console.error('Cant find user');}
    }
    catch(err){
        console.error('123');
        console.error(err.message);
        // return res.status(401).send({"error":{"message": autherr}});
    }
}
CheckLoginAndPassword();

Is that normal way to use it? Or we must clear execute PS cache somewhere, in e.x on application close(with sigterm)?
And i have more one question, why bit row's show as

active: <Buffer 01>,
    login_changed: <Buffer 00>

?

[sidorares]

Have a look at https://github.com/sidorares/node-mysql2/blob/master/documentation/Prepared-Statements.md

Can you exaplain clearing strategy?

LRU cache, 16000 by default but configurable via maxPreparedStatements config option. When expired, COM_STMT_CLOSE command is sent to server

Is prepared statements gonna save on mysql server or in node.js?

On the client it's just an integer representing statement Id returned from prepare step ( usually starting with 1 for each connection. The number is unique per connection but not unique across server ). For the server I guess it's much more

Is that normal way to use it? Or we must clear execute PS cache somewhere, in e.x on application close(with sigterm)?

I'm pretty sure server frees all resources used for PSs after connection is closed, so no need to close them manually.

 const someobj = await promisePool.execute('SELECT * FROM users WHERE login = ?', [login]);
        if(someobj.rows){//but this also true if array has no element
              console.log(someobj.rows);
        }

note that "someobj" here is NOT { rows, fields } object but [ rows, fields ] array. You can destructure it like const [rows] = await conn.execute(sql, params) or just access directly const result = await conn.execute(sql, params); console.log(rows[0][10].test, 'field "test" from row 10');

[afwn90cj93201nixr2e1re]

Thanks.

So. PSs saved on mysql server(then return id to client side driver(in this case mysql2)), and must be unique.
So

execute('SELECT * FROM users WHERE login = ?'...);

execute('SELECT * FROM users WHERE login = ?'...);

at same time will not create 2 duplicate PSs?)

After connection close all PSs by this connection will be released?

What 'bout bits?) Is there normal way to return true/false ?)

Also why execute return array, how i can get only rows object(without fileds returning)? Without like spread operations, is there some params for that?

[afwn90cj93201nixr2e1re]

 let login = 'abcd23';
        const [rows] = await promisePool.execute('SELECT * FROM users WHERE login = ? LIMIT 1', [login]);
        if(rows.length){
            console.log(rows[rows.length-1].login);

must be better

[sidorares]

at same time will not create 2 duplicate PSs?)

No, cached statement id from first one will be reused, only one prepare command and 2 execute

[sidorares]

After connection close all PSs by this connection will be released?

Yes

What 'bout bits?) Is there normal way to return true/false ?)

Not sure I understand the question, TINYINT returns 0/1, you need to convert to bool yourself

[sidorares]

Also why execute return array, how i can get only rows object(without fileds returning)? Without like spread operations, is there some params for that?

no, always array. As to why - probably to mirror callback style api (err, rows, fields) ( with async/await err is handled via standard exception mechanism )

[afwn90cj93201nixr2e1re]

Thx. That's all.