PHP - Mysql: storing images in DB - escaping special characters

Question

I read this tutorial about storing images in DB. In the tutorial, the author escapes special characters in the binary data before inserting: http://www.phpriot.com/articles/images-in-mysql/7 ( using addslashes although mysql_real_escape_string is preferable - but that is another issue ).

The point is, when displaying, he just displays the data as it is stored: http://www.phpriot.com/articles/images-in-mysql/8

My questions:

1) Do we need to escape special characters even for binary field type (blob)?

2) If so, then, do we not need to "unescape" the characters again in order to display the image correctly? (If so, what is the best way to do it. Any comments about efficiency? For large images: escaping and unescaping can be a big overhead?).

Or is it that my understanding about escaping is totally wrong (and escaping only affects the query and not the final data inserted/stored?).

thanks

JP

Guarneri

Answer 1

Your understanding of escaping is wrong. The data being inserted into the database is escaped, so that the query parser sees the information as intended.

Take the string "Jean-Luc 'Earl Grey' Picard". Escaping results in: 'Jean-Luc \'Earl Grey\' Picard'

When MySQL receives this, it understands that the escaped quotes need to be taken literally, that is what escaping means, and will store them in the database. It will not store the escape-characters in the database. The \ indicates to MySQL that it should take the character following it literally.

When retrieving, the data is presented to your application without the escaping characters, as they are removed when parsing the query.

Tribulation

Answer 2

1) Do we need to escape special characters even for binary field type (blob)?

Yes, because mysql_real_escape_string() (which is indeed the one to use) provides protection against SQL injection attacks, which could easily be inside an image file as well. Any arbitrary data you feed into a database must be sanitized first.

Dippold

Thanks Pekka. I guess we need to unescape before displaying then? And any thoughts on the efficiency? Just because of this escape-unescape, I am rethinking whether to store my image thumbnails in the database. – Guarneri

@Guarneri see #4248 for discussion – Dippold