CODEWITHSUNDEEP
mysqlstored-procedures
hafichuk
hafichuk

Reputation: 10781

Why can't I use a variable as the table name in a stored procedure?

I'm trying to use a variable as the table name within a stored procedure and it's using it as a string literal instead of as the actual table name. Why is this? Is there another way I should be doing this (aside from just doing it in PHP)?

DROP PROCEDURE IF EXISTS settonull;

DELIMITER //

CREATE PROCEDURE settonull()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE _tablename VARCHAR(255);
  DECLARE _columnname VARCHAR(255);
  DECLARE cur1 CURSOR FOR SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS table_name, COLUMN_NAME AS column_name FROM information_schema.COLUMNS WHERE IS_NULLABLE = 'YES' AND TABLE_SCHEMA = 'blip_notify' AND table_name = 'notify_queue' LIMIT 1;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO _tablename, _columnname;

    IF done THEN
      LEAVE read_loop;
    END IF;

    UPDATE _tablename SET _columnname = NULL WHERE LENGTH(TRIM(_columnname)) = 0;

  END LOOP;

  CLOSE cur1;
END//

DELIMITER ;

CALL settonull();

Output:

0 row(s) affected, 1 warning(s)

Execution Time : 0 sec
Transfer Time  : 1.094 sec
Total Time     : 1.095 sec

Note Code : 1305
PROCEDURE settonull does not exist
---------------------------------------------------

0 row(s) affected

Execution Time : 0.002 sec
Transfer Time  : 1.011 sec
Total Time     : 1.014 sec
---------------------------------------------------

Query: call settonull()

Error Code: 1146
Table 'blip_notify._tablename' doesn't exist

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.003 sec
---------------------------------------------------

Upvotes: 1

Views: 2523

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

Variables contain string values (or other data types), not table identifiers.

You can do what you want in a stored procedure if you concatenate the parts of the SQL query together as a string, and then PREPARE and EXECUTE that string as an SQL statement.

But FWIW, I would just do it in PHP.

Also be careful of SQL injection vulnerabilities when adding a table name dynamically to an SQL query, because escaping functions like mysql_real_escape_string() don't help for table names. See my solution for "Whitelist Maps" in my presentation SQL Injection Myths and Fallacies.

Upvotes: 3

goat
goat

Reputation: 31813

You need to use dynamic sql. ya, ugh.

SET @s = CONCAT('UPDATE ', _tablename, ' SET ', _columnname, ' = NULL WHERE LENGTH(TRIM(', _columnname, ')) = 0' );
PREPARE stmt FROM @s;
EXECUTE stmt;

Upvotes: 6

Related Questions