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;
|
|