How do I escape string on mysql using triggers?

Question

I need to store queries on another table and backup them, it works fine until some double quote (") or single quote (') comes in column values so it messes the query when I do a backup.

I have created an Insert before trigger on mysql

BEGIN
    SET @col_names_str = 'option_id , option_name , option_value , autoload';
    SET @col_vals_str = CONCAT('"', NEW.option_id, '"', ',', '"', NEW.option_name, '"', ',', '"', NEW.option_value, '"', ',', '"', NEW.autoload, '"');
    SET @cur_query = CONCAT('INSERT INTO `wp_options` ', '(', @col_names_str, ')', ' VALUES ', '(', @col_vals_str, ')', ';');
    INSERT INTO wp_query_recorder (id, query, table_name) VALUES (NULL, @cur_query, 'wp_options');
END

I want to escape the value of the column before inserting into another table.

For example data like this

"O:8:"stdClass":1:{s:12:"last_checked";i:1519369979;}"

to this

'O:8:\"stdClass\":1:{s:12:\"last_checked\";i:1519369919;}'

Something like this esc_sql(NEW.option_value) , is this possible to do?

Answer 1

I think you're looking for the QUOTE() function.