slashes appearing in database

[Andy50]

Hi

My hosting provider did some upgrade of PHP from 4.4.7 to 4.4.8 and possibly something to the MYSQL database and now I'm getting \ escape characters written to my database.

I checked and they had compiled the new build with magic quotes on, but they said that the individual php.ini file in my home directory would override that if it had magic_quotes_gpc set to Off.

This is what it is set to, and the php.info() shows them as off, and if I run;

if (get_magic_quotes_gpc())

      echo ON;

else echo OFF;

it reports them as OFF.

However as my application was working fine for the last couple of years with the use of functions such as mysql_real_escape_string() used to sanitise form input then their server change must have broken things.

If I remove the escape functions from my code then the data is written correctly to the MySQL database, but as I have over 1105 occurrences of escape spread over 80 different files I don't want to go changing them all.

Rather I would like to know what they have changed that could have caused this problem. Is there any other configuration settings or automatic filters that could cause this reversal of behaviour?

Please help it's driving me nuts.

 

[thephoneguy]

http://www.phpvideotutorials.com/lesson09/

18 minutes in check it out it should help you

[Wolphie]

Can we see some of the code you use to insert the data into the database?

[PFMaBiSmAd]

If you are seeing the \ escape characters when you retrieve the data using php code (you don't state how you know the \ characters are in the database), take a look at the magic_quotes_runtime setting -

 

magic_quotes_runtime

 

If enabled, most functions that return data from an external source, including databases and text files, will have quotes escaped with a backslash. Can be set at runtime, and defaults to off in PHP.

 

See also set_magic_quotes_runtime() and get_magic_quotes_runtime().

 

Your hosting company has taken a step in the wrong direction. The reason the default setting is off is because the magic quotes settings are depreciated and all of the magic quotes settings have been removed in php6. If your host already had the magic quotes settings off, they should have left them off so that they don't introduce more problems that will need to be eliminated under php6.

[Andy50]

Hi Guys

Impressed with the speed of response, however no nearer a solution.

I watched that video - seemed like a nice guy who waffles a lot, but didn't really help.

The code to put the data into the database is;

 

function sql($query) {

$res = mysql_query($query);

return $res;

}

 

 

$mysql_firstname = mysql_real_escape_string($firstname);

$mysql_lastname = mysql_real_escape_string($lastname);

$mysql_postcode = mysql_real_escape_string($postcode);

$mysql_phone = mysql_real_escape_string($phone);

$mysql_email = mysql_real_escape_string($email);

$mysql_age = mysql_real_escape_string($age);

$mysql_newsletter = ($newsletter == 'on')?'Yes':'No';

$mysql_gender = mysql_real_escape_string($gender);

$mysql_source = mysql_real_escape_string($source);

$mysql_customer = mysql_real_escape_string($listing['business']);

 

sql("

INSERT INTO Consumers (firstname, lastname, postcode, phone, email, age, newsletter, gender, source, customer)

VALUES ('$mysql_firstname', '$mysql_lastname', '$mysql_postcode', '$mysql_phone', '$mysql_email', '$mysql_age', '$mysql_newsletter', '$mysql_gender', '$mysql_source', '$mysql_customer')

");

 

Don't know why it was originally coded like this, but it's been working fine until they did a rebuild of the PHP core. This they deny but it clearly says this when I do a php.info();

 

PHP Version 4.4.8

 

System Linux cs.cs-server34.com 2.6.9-67.0.7.ELsmp #1 SMP Sat Mar 15 06:54:55 EDT 2008 i686

Build Date May 18 2008 08:49:32

Configure Command './configure' '--disable-mbregex' '--disable-posix' '--enable-bcmath' '--enable-calendar' '--enable-discard-path' '--enable-ftp' '--enable-gd-native-ttf' '--enable-libxml' '--enable-magic-quotes' '--enable-mbstring' '--enable-safe-mode' '--enable-sockets' '--prefix=/usr/php4' '--with-curl=/opt/curlssl/' '--with-freetype-dir=/usr' '--with-gd' '--with-gettext' '--with-imap=/opt/php_with_imap_client/' '--with-imap-ssl=/usr' '--with-jpeg-dir=/usr' '--with-kerberos' '--with-libxml-dir=/opt/xml2/' '--with-mcrypt=/opt/libmcrypt/' '--with-mhash=/opt/mhash/' '--with-mysql=/usr' '--with-mysql-sock=/var/lib/mysql/mysql.sock' '--with-openssl=/usr' '--with-openssl-dir=/usr' '--with-png-dir=/usr' '--with-ttf' '--with-xpm-dir=/usr/X11R6' '--with-zlib' '--with-zlib-dir=/usr'

Server API CGI

Virtual Directory Support disabled

Configuration File (php.ini) Path /home/biznames/public_html/php.ini

PHP API 20020918

PHP Extension 20020429

Zend Extension 20050606

Debug Build no

Zend Memory Manager enabled

Thread Safety disabled

Registered PHP Streams php, http, ftp, https, ftps, compress.zlib

 

Zend logo This program makes use of the Zend Scripting Language Engine:

Zend Engine v1.3.0, Copyright © 1998-2004 Zend Technologies with eAccelerator v0.9.5.2, Copyright © 2004-2006 eAccelerator, by eAccelerator with the ionCube PHP Loader v3.1.32, Copyright © 2002-2007, by ionCube Ltd., and with Zend Extension Manager v1.2.2, Copyright © 2003-2007, by Zend Technologies with Zend Optimizer v3.3.3, Copyright © 1998-2007, by Zend Technologies

 

Any more ideas??

 

[BlueSkyIS]

if you have extra slashes, either turn off magic_quotes, or stripslashes on input. assuming $firstname came from $_POST['firstname'] or similar:

 

$mysql_firstname = mysql_real_escape_string(stripslashes($firstname));

[Andy50]

Hi thanks

I forgot to mention that I am looking at the database via phpmyadmin and can see the slashes in the record fields, and of course the application web pages display it as well.

I believe magic_quotes are off as the hoster assures me that the local ini file overrides their compilation setting. The local php.ini has;

 

magic_quotes_gpc = Off

magic_quotes_runtime = Off

magic_quotes_sybase = Off

auto_prepend_file = /usr/local/php4/lib/php/formcheck.php

 

I am just wondering what this formcheck.php thing is in case it is some sort of preprocessor which is doing something like magic_quotes?

Anyone ever heard of it, or know what it does as I don't have access to the directory.

Guess I will have try and disable it and see if things change.

 

But any answers clues appreciated.......

 

[Andy50]

Oh well commented out the formcheck.php and it didn't alter the problem. Back to the drawing board. Guess my next trick will be to see if I can globally process all form input and stripslashes so that wherever the extra ones being added are removed before mysql_real_escape_string puts them back in. I really, really don't want to have to alter 80 different files to resolve an issue caused by a useless hosting company.

Anyone recommend hosting for PHP MYSQL environment?

[BlueSkyIS]

if you don't care about the host being canadian-owed: superb.net

[PFMaBiSmAd]

Create a script with a phpinfo(); statement in it to see what the actual runtime value for auto_prepend_file is.

 

If your hosting company turned on magic quotes, they might also be the ones who put the auto_prepend_file in the local php.ini and they might have put the same into the master php.ini.

 

It sounds like your hosting company is trying to fix something where the fix itself will break under php6.

 

BTW, if your hosting company does not have a schedule in place for upgrading to php5, you should begin the process of looking for a different host. The end of support for php4 was the end of 2007. Php4 is a dead end.

[PFMaBiSmAd]

Despite what the php manual states about magic_quotes_gpc, the recommend default is off.

 

The major changes in php6 are fairly well known. All the magic quotes settings have been removed.

 

Given the above information and that your host turned magic quotes on (and possibly has a prepend file adding slashes) after having them off for a couple of years, they are doing a huge disservice to their customers (you are probably not the only one who is currently having problems with existing code after they made the change.)

 

The reason magic quotes are being eliminated is because they don't escape all the special characters that can break a query (they do escape the ones that allow sql injection), so everyone needs to remove any slashes that magic quotes adds and any that some host is adding and then use the mysql_real_escape_string() function to prevent queries from failing due to any of the special characters being entered.

[Andy50]

Thanks guys.

I've run php.info() and it points to /usr/local/php4/lib/php/formcheck.php

which I don't have access to. I tried amending the local php.ini and commenting it out and then ran the forms again, but it didn't seem to make any difference so I assume that's not the problem. ANd of course magic_quotes_gpc etc is reported as Off so I'm stumped.

I guess I'm going to have to go through every file removing the mysql_real_escape_string function.

I bet by the time I've done this and cleaned up all the crap written to the database they'll fix whatever they've buggered up and I'll have to put all the old files back with the function back in.

Aaaaaaaaaaaagh. I've had enough for tonight.

Maybe the solution fairy will appear overnight. 

 

[PFMaBiSmAd]

Don't remove mysql_real_escape_string().

 

You need to do a stripslashes() on all the form data like BlueSkyIS suggested.

[Andy50]

Thanks for the suggestions guys. I added stripslashes as suggested, and still no joy. I'm going round in circles here.

[Andy50]

My code works fine locally running on PHP 5.2.1, but still has the \ problem when transferred to the live PHP 4.4.8 environment. I know it's a long shot but is there something significantly different in the way these versions work?

[PFMaBiSmAd]

I forgot to mention that I am looking at the database via phpmyadmin

 

phpmyadmin is a php script and would be affected by the magic_quotes_runtime setting. Do what I said in post #4 in this thread and check the magic_quotes_runtime setting.