Yes I’m aware that I can prepare statement but the connector will not resolve certain parameters correct due to statment type or data type.
For example a LIKE statement
SELECT * FROM sometable WHERE username LIKE :username:
will in simplifier not resolved to
SELECT * FROM sometable WHERE username LIKE ‘%userxy%’
It seemes that the LIKE statement is not support with prepared statements.
Another example is the IN statement with an ListOfString input parameter
SELECT * FROM sometable WHERE username IN (:usernamelist:)
will not resolved to
SELECT * FROM sometable WHERE username IN (‘user1’, ‘user2’, ‘user3’)
As an inspriation: How the mysql javascript implementation handles this:
Different value types are escaped differently, here is how:
Numbers are left untouched
Booleans are converted to true / false
Date objects are converted to ‘YYYY-mm-dd HH:ii:ss’ strings
Buffers are converted to hex strings, e.g. X’0fa5′
Strings are safely escaped
Arrays are turned into list, e.g. [‘a’, ‘b’] turns into ‘a’, ‘b’
Nested arrays are turned into grouped lists (for bulk inserts), e.g. [[‘a’, ‘b’], [‘c’, ‘d’]] turns into (‘a’, ‘b’), (‘c’, ‘d’)
Objects that have a toSqlString method will have .toSqlString() called and the returned value is used as the raw SQL.
Objects are turned into key = ‘val’ pairs for each enumerable property on the object. If the property’s value is a function, it is skipped; if the property’s value is an object, toString() is called on it and the returned value is used.
undefined / null are converted to NULL
NaN / Infinity are left as-is. MySQL does not support these, and trying to insert them as values will trigger MySQL errors until they implement support.
This escaping allows you to do neat things like this:var post = {id: 1, title: ‘Hello MySQL’};
var query = connection.query(‘INSERT INTO posts SET ?’, post, function (error, results, fields) {
if (error) throw error;
//
});