I had some shady characters hanging around my database at work yesterday…
So here’s the scenario:
I grab a heap of data in the form of comma delimited strings from and Interbase database on another server and then insert them as records in various MySQL tables on my reporting server. From there I do all kinds of things with the data. One of these things is to allow the download of the data as CSV files which can be opened by a spreadsheet package like Microsoft Excel.
The data consists of numbers, dates from the database, and numbers and text, which is all received via SMS. So normally we don’t see too many odd characters around.
All of a sardine I noticed that one of the records in my downloaded file split itself onto a new line half way through the text portion. When I looked in the MySQL database, it was evident that there were two spaces between two of the words instead of one.
When I looked in the source database, I found that the text portion had escaped characters in it.
“”1001″,”5″,”5″,”8″,”8″,”13″,”13″,”1907″,”300110″,”I am a \”real eager beaver\” when it come to strings””
So, I added stripslashes() to the function that sync’s the data, and voilà: problem solved.
Until I loaded the code onto the Production Server…
Turns out there is a strange configuration of magic_quotes in the php.ini. Not knowing if there was a specific reason for this, and because I am fairly cautious, I implemented the following in my script to ensure no further problems!
ini_set("magic_quotes_gpc", "Off"); ini_set("magic_quotes_runtime", "Off"); ini_set("magic_quotes_sybase", "Off");
Please note that as per the good folks at php.net: “This feature has been DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 6.0.0. Relying on this feature is highly discouraged.”
Lesson learned: always deal with data as explicitly as possible!