MYSQL - Execute procedure to execute statement from table

Question

I am working in a stored procedure that is fetching queries from a table and execute them. The problem is that I have some queries with single/doubled quotes and it is throwing an error on execute them.

Procedure

delimiter $$
drop procedure if exists run_change_ids_queries$$
create procedure run_change_ids_queries()
  begin

    declare s_query TEXT;

    declare done bool default false;
    declare c_queries cursor for
      select `query` from `queries` WHERE `executed` = 0 ORDER BY `qry_id` ASC;
    declare continue handler for not found set done = true;

    open c_queries;
    read_loop: loop

      fetch c_queries into s_query;
      if done then
        leave read_loop;
      end if;

      -- run the query
      set @sql = s_query;
      prepare stmt from @sql;
      execute stmt;
      deallocate prepare stmt;

      -- update executed flag on query
      set @update = CONCAT('UPDATE `queries` SET `executed` = 1 WHERE `query` LIKE \'',@sql,'\';');
      prepare stmt from @update;
      execute stmt;
      deallocate prepare stmt;
    end loop;
  end$$

Query update urisegments as s inner join change_product_ids as p on concat('{"product_id":"', p.old_id, '"}') = s.primary_key_value set s.primary_key_value = CONCAT('{"product_id":', p.new_id, '"}') where s.app_namespace = 'Shop' and s.primary_key_value like '%product_id%'; is throwing error:

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{"product_id":"', p.old_id, '"}') = s.primary_key_value set s.primary_key_value ' at line 1

Workaround #01
I already tried to escape single/doubled quotes into \' and \" respectively, but it throws another error: [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'{\"product_id\":\"\', p.old_id, \'\"}\') = s.primary_key_value set s.primary_k' at line 1.

This looks tough to build and 3 months from now tougher to maintain. At end of day, SQL is a set-based, declarative, special purpose language. Dynamic queries, loops, cursors are better run at application layer like Java, PHP, Python, etc. — Parfait
– Parfait, Commented Oct 17, 2018 at 21:14
yep @Parfait, I agree, but I am currently struggled in a cage with MySQL. This problem can only be fixed in a statement. — fiskolin
– fiskolin, Commented Oct 17, 2018 at 21:18

Barmar · Accepted Answer · 2018-10-17 21:14:07Z

2

Don't try to concatenate the query into the SQL. Prepared statements can contain placeholders, which you fill in when you use the EXECUTE statement.

set @update = 'UPDATE `queries` SET `executed` = 1 WHERE `query` = ?');
prepare stmt from @update;
execute stmt USING @sql;

answered Oct 17, 2018 at 21:14

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fiskolin · Accepted Answer · 2018-10-18 16:07:58Z

0

The statement is not escaped. All single/doubled quotes should be escaped.

update urisegments as s
inner join change_product_ids as p on concat(\'{\"product_id\":\"\', p.old_id, \'\"}\') = s.primary_key_value
set s.primary_key_value = CONCAT(\'{\"product_id\":\', p.new_id, \'\"}\')
where s.app_namespace = \'Shop\' and s.primary_key_value like \'%product_id%\';

answered Oct 18, 2018 at 16:07

fiskolin

1,4213 gold badges18 silver badges38 bronze badges

Comments

Rick James · Accepted Answer · 2018-10-25 01:45:36Z

0

Instead of testing for the query, test for its id:

... WHERE qry_id = ?

(Add that column to the initial SELECT.)

answered Oct 25, 2018 at 1:45

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

MYSQL - Execute procedure to execute statement from table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related