Page 1 of 1

Pear DB quoteSmart

Posted: Sat Apr 03, 2010 1:05 pm
by jcobban
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:

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);
which was generated by the following PHP code:

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"]) . ");";

Any suggestions on how to get around this?

Re: Pear DB quoteSmart

Posted: Sat Apr 03, 2010 2:50 pm
by jcobban
As an experiment I changed the code to use MDB2, but I get the same result.

Code: Select all

    $insCmd	= "INSERT INTO MarriageIndi (" . $flds . ") VALUES (" .
		"'" . $RegDomain . "', " .
		"'" . $RegYear . "', " .
		"'" . $RegNum . "', " .
		"'B', " .
		$connection->quote($Surname) . ", " .
		"SOUNDEX('" . $Surname . "'), " .
		$connection->quote($_POST["BGivenNames"]) . ", " .
		$connection->quote($_POST["BAge"]) . ", " .
		($_POST["RegYear"] - $_POST["BAge"]) . ", " .
		$connection->quote($_POST["BResidence"]) . ", " .
		$connection->quote($_POST["BBirthPlace"]) . ", " .
		$connection->quote($_POST["BMarStat"]) . ", " .
		$connection->quote($_POST["BOccupation"]) . ", " .
		$connection->quote($_POST["BFatherName"]) . ", " .
		$connection->quote($_POST["BMotherName"]) . ", " .
		$connection->quote($_POST["BReligion"]) . ", " .
		$connection->quote($_POST["Witness2"]) . ", " .
		$connection->quote($_POST["Witness2Res"]) . ", " .
	 	$connection->quote($_POST["BRemarks"]) . ");";

Re: Pear DB quoteSmart

Posted: Sat Apr 03, 2010 4:34 pm
by Eran
You probably have magic_quotes_gpc turned on, which leads to double escaping when used with database escaping functions. It is recommended to turn it off for this particular reason.

Re: Pear DB quoteSmart

Posted: Sun Apr 04, 2010 1:06 pm
by jcobban
pytrin wrote:You probably have magic_quotes_gpc turned on, which leads to double escaping when used with database escaping functions. It is recommended to turn it off for this particular reason.
Thank you. That solved the problem. :D Looking at the documentation it seems strange that magic_quotes_gpc is simultaneously defaulted on and deprecated at 5.3.0! My ISP is running 5.2.13 so I presume it wasn't yet deprecated at that release.