Pear DB quoteSmart

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jcobban
Forum Commoner
Posts: 41
Joined: Mon Mar 08, 2010 7:40 am

Pear DB quoteSmart

Post 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?
jcobban
Forum Commoner
Posts: 41
Joined: Mon Mar 08, 2010 7:40 am

Re: Pear DB quoteSmart

Post 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"]) . ");";
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Pear DB quoteSmart

Post 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.
jcobban
Forum Commoner
Posts: 41
Joined: Mon Mar 08, 2010 7:40 am

Re: Pear DB quoteSmart

Post 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.
Post Reply