mysql CONCAT keeps escaping

Question

Im trying to write a procedure, but in my @qr2 the CONCAT gets escaped when i pass the arg for the substring_index().

Any idea how i can pass the '-' into the function, without having it escaping the CONCAT function??

I have tried using "'-'" or \'-\' but nothing works, it keeps escaping it.

DELIMITER //
CREATE PROCEDURE filter_bar (IN bar_state VARCHAR(20), done_state VARCHAR(20))
BEGIN
set @qr1 = CONCAT('CREATE TABLE ', done_state, ' like ', bar_state, ';');
PREPARE smt from @qr1;
EXECUTE smt;
DEALLOCATE smt;

set @qr2 = CONCAT('UPDATE ', bar_state, ' SET date = SUBSTRING_INDEX(date,'-',-1);');
PREPARE smt from @qr2;
EXECUTE smt;
DEALLOCATE smt;

set @qr3 = CONCAT('INSERT into', done_state,'select name, business_id, date, sum(count) as count from', bar_state,' group by name, business_id, date;');
PREPARE smt from @qr3;
EXECUTE smt;
END //

Possible duplicate of [How to escape apostrophe (') in MySql?](https://stackoverflow.com/questions/9596652/how-to-escape-apostrophe-in-mysql) — , Sep 08 '18 at 18:36

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

You could use '':

set @qr2=CONCAT('UPDATE ',bar_state,' SET date=SUBSTRING_INDEX(date,''-'',-1);');

You also need a space:

set @qr3 = CONCAT('INSERT into', done_state,' select name, business_id, date, ...'
                                             -- here

And finally:

DEALLOCATE smt;
=> 
DEALLOCATE PREPARE smt;

DBFiddle Demo

EDIT:

To avoid problems with SQL Injection you should wrap each occurrence of identifier with quoting:

The quote_identifier() Function

Given a string argument, this function produces a quoted identifier suitable for inclusion in SQL statements. This is useful when a value to be used as an identifier is a reserved word or contains backtick (`) characters.

bar_state
=>
sys.quote_identifier(bar_state)

done_state
=>
sys.quote_identifier(done_state)

This is prone to SQL injections. – Raymond Nijland Sep 08 '18 at 19:09 — Raymond Nijland, Sep 08 '18 at 19:09

mysql CONCAT keeps escaping

1 Answers1