stored procedure not working php mysql

Question

I am trying to write a simple stored procedure for insert with parameter as table name and insert value.

DELIMITER $$
DROP PROCEDURE IF EXISTS TEST3; 
CREATE  PROCEDURE TEST3(IN tab_name VARCHAR(40),IN IDVALUE VARCHAR(40))
BEGIN
 SET @t2 = CONCAT('INSERT INTO ? (id) VALUES(?)');
 PREPARE stmt4 FROM @t2;
 EXECUTE stmt4 USING @tab_name, @IDVALUE;
 DEALLOCATE PREPARE stmt4;
END  $$
DELIMITER ;

It is being created but when I call it it throws following error:

ERROR 1064 (42000): 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 '? (id) VALUES(?)' at line 1

What is that I am doing wrong.

peterm · Accepted Answer · 2014-12-26 00:49:20Z

3

If you need this you can do it like this

DELIMITER $$
CREATE  PROCEDURE TEST3(IN tab_name VARCHAR(40), IN IDVALUE VARCHAR(40))
BEGIN
 SET @t2 = CONCAT('INSERT INTO `', tab_name, '` (id) VALUES(?)'),
     @id = idvalue;

 PREPARE stmt4 FROM @t2;
 EXECUTE stmt4 USING @id;
 DEALLOCATE PREPARE stmt4;
END$$
DELIMITER ;

Here is a SQLFiddle demo

Two major issues with your original code are:

You can't prepare a statement without an object identifier, table name in your case. Object identifiers are static. To work around this you should concatenate a table name into your statement.
Local (IDVALUE in your case) and user (session) variables (@IDVALUE) are two completely different beasts. And EXECUTE ... USING can work only with latter. Therefore you need to assign a value of IDVALUE to a session variable (@id in my example).

2 Comments

user2850305 Over a year ago

perfect answer. i had tried with concatenate 'tab_name' but had no idea about the second issue that u mentioned.

user2850305 Over a year ago

is this statement immune to sql injection with respect to parameter IDVALUE?

Sakthikanth · Accepted Answer · 2017-06-09 12:53:33Z

0

Try running this query, it worked for me in phpmyadmin

CREATE PROCEDURE `sample_procedure`(IN `10` INT) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER SELECT "print this";

answered Jun 9, 2017 at 12:53

Sakthikanth

1397 bronze badges

Collectives™ on Stack Overflow

stored procedure not working php mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related