PREPARE an INSERT containing HTML

from the Artful MySQL Tips List


There are arguments against storing HTML in databases, but sometimes it's hard to avoid. A MySQL forum poster wanted to do so in a table named at runtime but had trouble getting the MySQL client to accept a PREPAREd statement containing the data


<p align="right">test1</p>



A solution offered by Wm Chiquito was to turn ANSI_QUOTES on, then surround the problematic string value with three single quotes:


DROP TABLE IF EXISTS t;

CREATE TABLE t(id INT, value CHAR(64));

INSERT INTO t VALUES(1,'original value');

SELECT * FROM t;

+------+----------------+

| id   | value          |

+------+----------------+

|    1 | original value |

+------+----------------+



DELIMITER |

DROP PROCEDURE IF EXISTS prepareInsert;

CREATE PROCEDURE prepareInsert( pTable CHAR(64), pID INT, pvalue CHAR(128))

BEGIN

  SET @sql = CONCAT('UPDATE ', ptable, ' SET value=''', pvalue, ''' WHERE id=', pID );

  PREPARE stmt FROM @sql;

  EXECUTE stmt;

  DROP PREPARE stmt;

END |

DELIMITER ;



SET @sql_mode = @@sql_mode;

SET sql_mode = ANSI_QUOTES;

CALL prepareInsert( 't', 1, '"<p align="right">test1</p>"' );

SELECT * FROM t;

+------+------------------------------+

| id   | value                        |

+------+------------------------------+

|    1 | "<p align="right">test1</p>" |

+------+------------------------------+

SET sql_mode = @sql_mode;




Return to the Artful MySQL Tips page