MySQL 存储过程中的准备更新语句

Question

我必须更新多个数据库中的多个表记录。当我使用准备好的语句并执行时它不起作用。但是当我执行该语句时它就起作用了。

set @u = concat("Update `",pDB_NAME,"`.`",pTABLE_NAME,"` set 
        `NAME` ='",pNAME,"',
        `FATHER` ='",pFATHER,"',
        `REGNO` ='",pREGNO,"',
        `SEX` ='",pSEX,"',
        `STATUS` ='",pSTATUS,"',
        `DOB` ='",pDOB,"',
        `DISTT` ='",pDISTT,"',
        `NOC_ISSUED` ='",pNOC_ISSUED,"',
        `ADDRESS` ='",pADDRESS,"',
        `CONTACTNO` ='",pCONTACTNO,"',
        `CNIC` ='",pCNIC,"',
        `FCNIC` ='",pFCNIC,"',
        `SPECIALITY` ='",pSPECIALITY,"',
        `NATIONALITY` ='",pNATIONALITY,"',
        `RELIGION` ='",pRELIGION,"',
        `MEDIUM` ='",pMEDIUM,"',
        `DISTT_CODE` ='",pDISTT_CODE,"',
        `TEH_CODE` ='",pTEH_CODE,"'

          WHERE RNO='",pRNO,"';");
           PREPARE stmt3 FROM @u;

以下语句返回完美查询

   select @u; 

但是下面这一段没有结果

        EXECUTE stmt3;
        DEALLOCATE PREPARE stmt3;

Answer 1

不要通过将变量值括在带引号的字符串中的引号中来引用列值。它很草率，它可以掩盖错误，并为 SQL 注入(inject)打开了大门。使用QUOTE()功能。

    ...
    `NAME` =",QUOTE(pNAME),",
    `FATHER` =",QUOTE(pFATHER),",
    `REGNO` =",QUOTE(pREGNO),",
    ...

此结构还可以正确处理转义和 NULL值正确，而朴素的引号连接则不然。

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_quote