Need assistance with escaping of " and ' characters in form fields

Good day, and thanks in advance for any assistance. I'm trying to INSERT some form info into a MySQL DB and I can't seem to get the "escaping" of quotation marks and apostrophes correct. I'm on shared hosting with magic quotes off, so I've used mysqli_real_escape_string in my code, which follows...

include ('userincludes/mysqli_connect_to_kap_db.php'); //Connect to DB
print "The following are printed from the POST array after that element is escaped.<br /><br />"; //For observation - my test string is OK at this point...
foreach ($_POST as $key=>$value)
{
$value = mysqli_real_escape_string($conn, $value); //$conn is the DB connection string...
print "$key - "; print "$value<br />"; //For observation...
}
reset($_POST);
$query = "INSERT INTO tbl_kaplistings (Lots of field names here in the form fld_whatever) VALUES ( Lots of values, in the form '$_POST[elementnamegoeshere]', etc. ')";
print "We've just passed the query string, but we're not yet to the result confirmation.<br />"; //For observation...
$result = mysqli_query($conn, $query) or die ('Could not record your information: ' . mysqli_error($conn));

This is the test string - " This is a "test listing" to see if this listing will work. Mark's ". The value in the array appears to be properly escaped - the value of the array variable is - "This is a \"test listing\" to see if this listing will work. Mark \'s ". But I'm getting an error message as follows - "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's', '')' "

In other words, the slash before the single quotes is lost between the time that the array value is printed and the moment when the INSERT attemp occurs. I've been messing with this for a few days, on and off, using addslashes, other mysql commands and mysql_real_escape_string . The same error persists at the same point in the string. I should add that not all of the $_POST variables have values; in my testing, I have left many of them as null.

Any suggestions will be appreciated. Thank you.

Steve E.

 

$value is a COPY of the array index, NOT the original. As such you are NOT actually escaping it in a useful manner.

There are two ways around this, forcing $value to be a reference instead of a copy:

foreach ($_POST as $key => &$value) {
	$value = mysqli_real_escape_string($conn,$value);
}

Code (markup):

or making use of that key:

foreach ($_POST as $key => $value) {
	$_POST[$key] = mysqli_real_escape_string($conn,$value);
}

Code (markup):

NOT that you really should be using any of that since you seem to be using mysqli, so use prepared queries to pass the values safely instead of wasting processing time on the somewhat less secure way of handling it.

 

^ use prepared statements, thats the real strength of PDO and MySQLi

 

Thanks to you both! I didn't know that prepared statements and PDO existed until Saturday, and haven't yet looked into them. Guess that's a little higher on the list now. One more question, if you will - how is $value a copy of the index, and not the original? Hope I asked that right...

Thank you again.

Steve

 

I believe that passing by reference throws a warning in PHP 5.3. I guess they're trying to keep everything object based.