typeorm: Raw SQL Query - Parameter Not Working

if you are running raw sql queries you use escaping mechanizm provided by underlying driver. In your case its mysql and you must use its escaping mechanizm:

const result = await db.entityManager.query("select id, email, name from users where id=?", [id])

Refer to official mssql package documentation:

getConnection().query("SELECT * FROM Company WHERE Name = @0", ['Some Company']);

I know this is closed, but for future reference, the answer depends on the underlying database drivers:

• Oracle: query('SELECT * FROM table WHERE name = :name', [ { val: 'something' } ])
• MySQL: query('SELECT * FROM table WHERE name = ?', [ 'something' ])
• MSSQL: query('SELECT * FROM table WHERE name = @0', [ 'something' ])
• Postgres: query('SELECT * FROM table WHERE name = $1', [ 'something' ])

same answer applies to all databases.

Hi guys,

How can i provide array for IN? If i provide like a IN(?) it doesn’t work.

I tested with query builder and it does like IN(?, ?) if i have two element on array.

This is a slightly different question but I’m wondering if there is any way to supply the table name as a parameter, in this case specifically for postgresql. Like:

getConnection().query("SELECT * FROM $1 WHERE Name = 'john'", ['Company']);

It seems that this is not allowed by the underlying database driver and I’m wondering if there is a proper way to do this maybe using some escaping from the typeorm library-

@adilets I used this hack because I really don’t want to spend any time on basic stuff like this. I can’t believe that I can’t pass an array of numbers in a simple way. The issue with just (?) is that the array gets stringified with an extra , in the end and that’s not valid Mysql syntax.

		// Parameters are passed with an ugly hack because just (?) and an array doesn't work.
		// The array gets stringified with an extra ',' in the end and that breaks Mysql syntax.
		const sessions = await this.manager.query(
			`select * from sessions where id in (${sessionIds.map(() => '?')})`,
			[...sessionIds]
		)

My DB is Postgres and using (?) didn’t work out.

So I had to do this

this.repository.query(`select * from users where LOWER(first_name) = '${first_name.toLocaleLowerCase()}' OR LOWER(last_name) = '${last_name.toLocaleLowerCase()}'`)

That’s very unsafe and can be exploited trivially. You should really be using parameters for this instead.

this.repository.query(`select * from users where LOWER(first_name) = $1 OR LOWER(last_name) = $2`, [ first_name.toLocaleLowerCase(), last_name.toLocaleLowerCase() ])

(${sessionIds.map(() => ‘?’)})

Using postgres u need write code like that:

         where e."employeeId" in (${employeesArr.map(
               (employeeId, index) => `$${index + 1}`,
             )}), employeeArr

After in your sql query you will see params $X ( depens how many value in array you have ).

I’m wondering if this kind of approach is at all suggested, I just need to support postgres and sqlite dbs, and I’m pretty sure these kinds of queries would execute in both dbs, but they have different syntax for the parameter placeholders, so I tried this, and it did work, but I’m wondering if this isn’t recommended for any particular reasons…

let dbParamFmt = isPostgres ? "$" : "?";

return this.myRepository.query(`
    select m.sex_code, sum(ma.death_num) as sum_death_num, ma.death_age_code from mortality m
        join mortality_age ma using (mortality_id)
        where m.health_code != ${dbParamFmt}4
        and m.user_id = ${dbParamFmt}1
        and m.region_setting = ${dbParamFmt}2
        and m.year4 = ${dbParamFmt}3
        group by m.sex_code, ma.death_age_code
`,[filters.user_id,filters.region_setting,filters.year4,filters.health_code]);

I’m pursuing this because I’m not sure I really see any big benefit to using querybuilder in this case, since it appears I don’t really get any benefit there since there’s no real type checking of column names (unlike the Repository find approach). Am I missing something?

same answer applies to all databases.

PostGres didn’t follow the ? convention rather it had to have $1,.... format

Ah, right, of course I could do this:

getConnection().query(`SELECT * FROM ${myTableName} WHERE Name = 'john'`);

But then I get no protection from sql injection. What would comprehensive escaping of a table name look like in this case?

Hmm, ok, are you saying to do it like this? (I’m on postgres, so it should be double quotes I believe)

getConnection().query("SELECT * FROM "$1" WHERE Name = 'john'", ['Company']);

That also results in an error:

relation “$1” does not exist

Hey guys,

I know this is closed, but I’m having trouble with raw sql query parameters for the mssql package.

I’ve tried the below combinations:

getConnection().query("SELECT * FROM Company WHERE Name = ?", ['Some Company']); getConnection().query("SELECT * FROM Company WHERE Name = @name", ['Some Company']); getConnection().query("SELECT * FROM Company WHERE Name = $1", ['Some Company']);

None of these work. Can someone point me in the right direction?

Thanks!