Escaping without quotation marks

[BlackCetha]

I'm using this library and mysql.js in a project of mine. Right now, I'm working on a usernamesearching function.

This is my current code:

/** Search for a name or part of names */
export function search ( nameScheme: string, callback: ( error: Error | void, matches?: User.Name.DB[] ) => void ): void {
    db.query( `SELECT * FROM usersNames WHERE name LIKE ${ sqlstring.escape( nameScheme ) }`, ( err, result ) => { // TODO: Insert wildcard chars in nameScheme
        if ( err ) return callback( err );

        callback( null, result );
    } );
}

After writing this, it struck me that the library doesn't give me the option to disable the automatic insertion of quotation marks around the passed string. I read through the issues here on github and understand why it's this way, but it makes the library useless in this case, which I believe to be quite common. I would at least expect the option to disable automatic quotation marks.

How should I work around this? Is making this feature optional a possible thought for you?

[dougwilson]

Hi @BlackCetha I'm not sure I understand -- the quotes would need to be around what comes after the LIKE string above, right?

You have SELECT * FROM usersNames WHERE name LIKE ${ sqlstring.escape( nameScheme ) } which would need to become SELECT * FROM usersNames WHERE name LIKE 'whatever_is_in_namescheme', yes? If not, what am I missing? What is the contents of nameScheme and what are you trying to get the resulting SQL to look like?

[BlackCetha]

The generated SQL query should look like this: SELECT * FROM usersNames WHERE name LIKE "%exampleString%"

I will have to insert the wildcard characters between the quotation marks for this to work. I would much prefer it if I could just add them myself later on.

[dougwilson]

Ok. So when your variable nameScheme is '%exampleString%' it is working fine, right? I'm not sure I understand the issue. Can you maybe help me understand how my below code is wrong or how it is not generated the SQL you are expecting?

$ node -pe 'sqlstring = require("sqlstring"); nameScheme = "%exampleString%"; `SELECT * FROM usersNames WHERE name LIKE ${ sqlstring.escape( nameScheme ) }`'
SELECT * FROM usersNames WHERE name LIKE '%exampleString%'

[dougwilson]

@BlackCetha please don't dd critical follow up in an edit; edits are not emailed to me and don't let me know you did it, so good thing I happened to re-read back up after my reply.

[dougwilson]

What is wrong with the following?

db.query('SELECT * FROM usersNames WHERE name LIKE ?', [`%${ nameScheme }%`], ( err, result ) => {

[BlackCetha]

I guess it just feels unclean. To me the logical order of operation would be sanitation first, formatting second.

[dougwilson]

And just using this module instead of the db.query:

$ node -pe 'sqlstring = require("sqlstring"); nameScheme = "exampleString"; sqlstring.format("SELECT * FROM usersNames WHERE name LIKE ?", [`%${ nameScheme }%`])'
SELECT * FROM usersNames WHERE name LIKE '%exampleString%'

I hope this helps!