PHP function to escape necessary characters

[Molson31]

Hello. My client and I are trying to figure out a strange issue with exporting blobs. We are trying to sync a WAMP based databse and local MySQL database. The problem is with blob columns. 

The issue: 

When exporting a blob, the local MySQL dump command inserts an escape character "\" before characters such as NUL, TAB and LF, and I am assuming a lot more. My PHP export function (opens a file, SELECT * FROM tbl, writes to the file) does not do this. 

The main issue is that my client cannot import my PHP dump files when there are blobs, which I suppose is because they are missing these escape characters. He can however re-import his own MySQL dumps. 

I know I could remedy by using PHP string replace functions to find each character and insert a '\' before it, but I don't know which characters will need to be escaped. I also feel like there is a more simple solution. For example this is what I have and it worked for a tiny 1 pixel image, but not for something complex:

$val = str_replace(chr('NUL'), '\0', $val);							
$val = str_replace(chr(0x9), chr(0x5c).chr(0x9), $val);	
$val = str_replace(chr(0xa), chr(0x5c).chr(0xa), $val);	

Thanks

[Jacques1]

Why are you even trying to write your own implementation of mysqldump?

[Molson31]

Because I need the file to be saved to the user's PC, not where the MySQL database is located.

[Jacques1]

So? Install mysqldump in your PHP environment and run it from there.

[bsmither]

It's a 'blob' but you seem to be treating/thinking of the contents as a string. While a string can hold non-ascii characters, they will be a problem when trying to print them. They should not be problem when saving the string to a file.

 

When I don't know how my data will affect its container, I base64_encode() it first.

 

But I am not fully understanding what you are doing where you feel the need to escape certain bytes (control codes).

[Molson31]

So? Install mysqldump in your PHP environment and run it from there.

 

I can't do something like that. This is on typical business web hosting.

[Molson31]

It's a 'blob' but you seem to be treating/thinking of the contents as a string. While a string can hold non-ascii characters, they will be a problem when trying to print them. They should not be problem when saving the string to a file.

 

When I don't know how my data will affect its container, I base64_encode() it first.

 

But I am not fully understanding what you are doing where you feel the need to escape certain bytes (control codes).

 

There are two database dumps in question

 

A) Local MySQL

- When it exports blobs, it inserts escape character "\" before many characters such as LF, TAB and NUL

 

B) Webhost MySQL

- Using a custom PHP function, when it exports a blob, it does not have these escape characters

 

I am trying to understand what this means and how to make either one like the either. At this point I am thinking the best way is to insert escape characters in the Webhost dump, so I guess the next question is which ones. I am wondering as well if this is something built-in somewhere that someone may know of.

Edited December 30, 2014 by Molson31

[bsmither]

Please look at these MySQL Doc pages:

http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_sql-mode

http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_backslash_escapes

[Molson31]

Please look at these MySQL Doc pages:

http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_sql-mode

http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_backslash_escapes

 

Thanks will try this!

[kicken]

I can't do something like that. This is on typical business web hosting.

Ask your host if mysqldump is already available for you to use. They may have it installed already and give you the information necessary to use it.

 

If you're absolutely stuck using PHP to dump the data though, then I would hex encode the blob columns. Then you don't have to worry about what characters need to be escaped vs which are ok as is. Just encode everything.

[Molson31]

Thanks.

 

I am also wondering, does this have  anything to do with character sets/collation? 

 

Also mysqldump only exports locally so it is still not useable for me. I need the file to go somewhere.

Edited December 30, 2014 by Molson31

[CroNiX]

You can always run it from a script and when it's complete email it somewhere or whatever you need to have it do. You can also just do it from a cronjob like this.

[kicken]

Also mysqldump only exports locally so it is still not useable for me. I need the file to go somewhere.

No it doesn't. It dump's to stdout, when you can then stream from your script to the end user directly.