const key = CryptoJS.PBKDF2(passphrase, salt, { hasher: CryptoJS.algo.SHA512, keySize: 512/32, iterations: 1000});
const sql = `
SELECT
p.PRIVILEGE_LEVEL_NAME, user.USER_ID, user.USER_NAME ,user.PERMITTED_FUNCTIONS
FROM
user_priviledge_table p
INNER JOIN
user_account_table user on user.PRIVILEGE_LEVEL = p.PRIVILEGE_LEVEL
WHERE
user.user_name='${req.body.user_name}' and user.password='${key}' LIMIT 1
`;
const [payload] = await db.query(sql);
It would equal to the same result as:
const key = CryptoJS.PBKDF2(passphrase, salt, { hasher: CryptoJS.algo.SHA512, keySize: 512/32, iterations: 1000});
const sql = `
SELECT
p.PRIVILEGE_LEVEL_NAME, user.USER_ID, user.USER_NAME ,user.PERMITTED_FUNCTIONS
FROM
user_priviledge_table p
INNER JOIN
user_account_table user on user.PRIVILEGE_LEVEL = p.PRIVILEGE_LEVEL
WHERE
user.user_name=? and user.password=? LIMIT 1
`;
const [payload] = await db.query(sql, [req.body.user_name, key.toString(CryptoJS.enc.Hex)]);
I want to know how db.query(sql) could hex the WordArray object to String and the exact difference between these two methods.
Which one would be better to use?
query() method is structured by this way:
Connection.prototype.query = function query(sql, values, cb) {
var query = Connection.createQuery(sql, values, cb);
query._connection = this;
if (!(typeof sql === 'object' && 'typeCast' in sql)) {
query.typeCast = this.config.typeCast;
}
if (query.sql) {
query.sql = this.format(query.sql, query.values);
}
if (query._callback) {
query._callback = wrapCallbackInDomain(this, query._callback);
}
this._implyConnect();
return this._protocol._enqueue(query);
};
createQuery()
Connection.createQuery = function createQuery(sql, values, callback) {
if (sql instanceof Query) {
return sql;
}
var cb = callback;
var options = {};
if (typeof sql === 'function') {
cb = sql;
} else if (typeof sql === 'object') {
options = Object.create(sql);
if (typeof values === 'function') {
cb = values;
} else if (values !== undefined) {
Object.defineProperty(options, 'values', { value: values });
}
} else {
options.sql = sql;
if (typeof values === 'function') {
cb = values;
} else if (values !== undefined) {
options.values = values;
}
}
if (cb !== undefined) {
cb = wrapCallbackInDomain(null, cb);
if (cb === undefined) {
throw new TypeError('argument callback must be a function when provided');
}
}
return new Query(options, cb);
};
check files
On the other hand I think that the two methods you describe are technically the same, the difference is the way the values are passed, also normally in the documentation we can find 3 ways to invoke query().
The simplest form of .query() is .query(sqlString, callback), where a SQL string is the first argument and the second is a callback:
connection.query('SELECT * FROM `books` WHERE `author` = "David"', function (error, results, fields) {
// error will be an Error if one occurred during the query
// results will contain the results of the query
// fields will contain information about the returned results fields (if any)
});
The second form .query(sqlString, values, callback) comes when using placeholder values (see escaping query values):
connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) {
// error will be an Error if one occurred during the query
// results will contain the results of the query
// fields will contain information about the returned results fields (if any)
});
The third form .query(options, callback) comes when using various advanced options on the query, like escaping query values, joins with overlapping column names, timeouts, and type casting.
connection.query({
sql: 'SELECT * FROM `books` WHERE `author` = ?',
timeout: 40000, // 40s
values: ['David']
}, function (error, results, fields) {
// error will be an Error if one occurred during the query
// results will contain the results of the query
// fields will contain information about the returned results fields (if any)
});