Pear DB quoteSmart
Posted: Sat Apr 03, 2010 1:05 pm
I am entering data in a form which is then inserted into a MySQL database. At the moment I am using PEAR DB because it was discussed in the O'Reilly book, although it is now superseded by MDB2.
Since the user may enter values that include characters that are meaningful to the SQL language DB includes a function quoteSmart which is described as being for this precise purpose. However I cannot get it to work as described because it "over-escapes" the quotes. A single quote "'" is replaced by "\\\'", as demonstrated by the following command:
which was generated by the following PHP code:
Any suggestions on how to get around this?
Since the user may enter values that include characters that are meaningful to the SQL language DB includes a function quoteSmart which is described as being for this precise purpose. However I cannot get it to work as described because it "over-escapes" the quotes. A single quote "'" is replaced by "\\\'", as demonstrated by the following command:
Code: Select all
INSERT INTO MarriageIndi (M_RegDomain, M_RegYear, M_RegNum, M_Role, M_Surname, M_SurnameSoundex, M_GivenNames, M_Age, M_BYear, M_Residence, M_BirthPlace, M_MarStat, M_Occupation, M_FatherName, M_MotherName, M_Religion, M_WitnessName, M_WitnessRes, M_Remarks) VALUES ('CAON', '1897', '9763', 'B', 'mcDonald', SOUNDEX('mcDonald'), 'jane', '21', 1876, 'mosa', 'mosa', 's', 'farmer\\\'s daughter', 'robert mcDonald', 'elizabeth Murguson', 'presbyterian', NULL, NULL, NULL);Code: Select all
$insCmd = "INSERT INTO MarriageIndi (" . $flds . ") VALUES (" .
"'" . $RegDomain . "', " .
"'" . $RegYear . "', " .
"'" . $RegNum . "', " .
"'G', " .
$connection->quoteSmart($Surname) . ", " .
"SOUNDEX('" . $Surname . "'), " .
$connection->quoteSmart($_POST["GGivenNames"]) . ", " .
$connection->quoteSmart($_POST["GAge"]) . ", " .
($_POST["RegYear"] - $_POST["GAge"]) . ", " .
$connection->quoteSmart($_POST["GResidence"]) . ", " .
$connection->quoteSmart($_POST["GBirthPlace"]) . ", " .
$connection->quoteSmart($_POST["GMarStat"]) . ", " .
$connection->quoteSmart($_POST["GOccupation"]) . ", " .
$connection->quoteSmart($_POST["GFatherName"]) . ", " .
$connection->quoteSmart($_POST["GMotherName"]) . ", " .
$connection->quoteSmart($_POST["GReligion"]) . ", " .
$connection->quoteSmart($_POST["Witness1Name"]) . ", " .
$connection->quoteSmart($_POST["Witness1Res"]) . ", " .
$connection->quoteSmart($_POST["GRemarks"]) . ");";