How can i escape variable in MySQL query?

dragon1993 · December 6, 2020, 12:08am

How can i escape variable in MySQL query?
I don’t find any way to prevent mysql injection.

Miquel_Colomer · December 6, 2020, 9:15am

Hi @dragon1993,

Have you tried this?

dragon1993 · December 6, 2020, 8:05pm

I think QUOTE() function doesn’t protect from the SQL injection.
I have input data from the previous node and I want use this data in MySQL query but I can’t escape the input.

github.com

n8n-io/n8n/blob/d3fd88c3eb34e1149cda418c3b72b211a232705e/packages/nodes-base/nodes/MySql/MySql.node.ts#L191

      
        
            		let returnItems = [];
            
            
		if (operation === 'executeQuery') {
            			// ----------------------------------
            			//         executeQuery
            			// ----------------------------------
            
            
			const queryQueue = items.map((item, index) => {
            				const rawQuery = this.getNodeParameter('query', index) as string;
            
            
				return connection.query(rawQuery);
            			});
            			let queryResult = await Promise.all(queryQueue);
            
            
			queryResult = queryResult.reduce((collection, result) => {
            				const [rows, fields] = result;
            
            
				if (Array.isArray(rows)) {
            					return collection.concat(rows);
            				}

I miss the MySQL2 Prepared Statements

Miquel_Colomer · December 7, 2020, 7:36am

Another option could be creating a Javascript function like this

Putting sample code in a function node and escaping dangerous characters will do the job.

dragon1993 · December 7, 2020, 10:08am

It’s can work in my case, but i have lot of time i will make the prepared statement support for the mysql node.