Prepared statement is changing POST data

I have a situation where I am using a prepared statement to update a table with an insert. The $POST is “Bob’s Company” and when it gets to the database it becomes Bob’s Company.

I check the value of the post variable before and after the execute and it is Bob’s in both places but when I look in the db table it is Bob_'s.

Dan anyone point me in the right direction here? Thanks

Looks like magical quotes but using an underscore to escape? Print out the POST array and see if that underscore is being added before the binding occurs. If that is the case it looks like something to do with magical quotes being configured awkwardly. Otherwise, its probably something to do with the php configuration. In which case you may have to change your approach or find a new host.

Let me add a clarification to this. I am running a test environment with mysqli extensions but my hosting company is running mysql I believe. This happened when I switched to prepared statements, so it works fine in the testing environment, but when I run it in the hosted environment is when I get the problem. Normal selects work fine, etc. And the database updates okay, it is just adding in the extra _.

It looks like it is trying to escape the ’ in the binding or execution phase but that is failing to add it to the database properly. Just curious if anyone had seen anything like this.

Thanks

oddz, you’re right. Turns out I have access to my php.ini file and evidently the default was for magicquotes on. I set it to off and all seems to be okay now.

Thanks to all

Here is the code but remember, it runs just fine in my test environment. So it seems unlikely it’s a problem with the code. I have a feeling this is a driver issue because it seems to work

$qt_sql = "INSERT INTO venue (name, address, city) 
		VALUES (?, ?, ?)";

if (!($prep1 = $link->prepare($qt_sql)))
{
	$_SESSION['message'] = 'Error preparing to add the new restaurant. Error -- 0308 ---- ';
	header("Location: " . $_SESSION['uwserver'] . "/System_Error");
	exit();
}
$prep1->bind_param("sss", $_POST['addrestname'], $_POST['addstreetname'], $_POST['addcityname']);

if (!$prep1->execute())
{
	$_SESSION['message'] = 'Error adding the new restaurant. Error -- 0309 ---- ' . $link->error;
	header("Location: " . $_SESSION['uwserver'] . "/System_Error");
	exit();
}

$prep1->close();

If I run this code it updates just fine

$qt_sql = "INSERT INTO venue (name, address, city) 
		VALUES ('Bob\\'s New Place inner', 'street', 'city')";

if (!mysqli_query($link, $qt_sql))
{
	$_SESSION['message'] = 'Error adding new review.';
	header("Location: " . $_SESSION['uwserver'] . "/System_Error");
	exit();
}

So it strikes me that on the web host they are using older drivers and the drivers are attempting and failing to do an escape on the '. Does that make sense? Well they have admitted to using older drivers so I guess the question is is it reasonable to assume that’s the problem.

Also, anyone got a good recommendation for a hosting company that can start small and provided significant growth?

It might be possible to figure out by looking at the code but without seeing the code there is no information to work with.

Yes, sorry, should have mentioned that. I did intercept and print out the post before it went into binding and it looks fine so it is definitely happening during the bind or the execute. There is simply no other place for it to happen. I really do suspect this is a driver issue of some sort. But obviously it supports MySqli at some level because the procedural query works.

Tried issuing it as a prepared statement but with procedural mysqli rather than Oo and interestingly enough, it now inserts a \ in front of the '. So the OO inserts an underscore and the procedural inserts a backslash.

Is there any way to see the actual sql that gets submitted when you are binding?