problem saving image urls to databse table

Question

I am trying to save image urls to a MySQL database table

The column field is long enough. The table and database are using UTF-8 CI-general collation (IIRC)

The urls look something like this:

http://example.com/media/images/47142000/jpg/_47142379_005857853-1.jpg

but they get saved like this:

http://example.com/media/images/47142000/jpg...

it would seem that the apprearance of the '_' is causing mySQL problems. Do I need to urlencode the string (or perform some other data munging on the string) before saving it to the database?

Answer 1

Two solutions: 1) call mysql_real_escape_string() - not sure what language you are using but this is part of the mysql client so most languages will support it.

2) use prepared statements. eg: INSERT INTO image_table(url) values(?) and then bind the variable. All special characters will be automatically escaped.

Answer 2

Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (“\”), known as the escape character. MySQL recognizes the following escape sequences.

\0 An ASCII NUL (0x00) character. \' A single quote (“'”) character. \" A double quote (“"”) character. \b A backspace character. \n A newline (linefeed) character. \r A carriage return character. \t A tab character. \Z ASCII 26 (Control-Z). See note following the table. \ A backslash (“\”) character. \% A “%” character. See note following the table. _ A “_” character. See note following the table.

You have to escape the _ character.

See http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html for more information