CODEWITHSUNDEEP
phpmysql
Adrian Tanase
Adrian Tanase

Reputation: 700

should quotes be saved escaped in MySQL?

I am working on a portal and I have these few questions regarding saving data in MySQL tables :

  1. Should I save varchar field escaped ? i'm using now mysql_real_escape_string() for avoiding string-injection.

  2. Why should I save them unescaped (this was proposed by a guy on this website) and how would that work for characters like single and double-quotes. Doesn't it wreck the SQL command ?

easy talking around this topic.

And one last thing....I was using addslashes and stripslashes before using mysql_real_escape_string and it worked for me (of course, with mysql-injection of malicious code chance, which I recently discovered and documented myself on it)...

thanks

Upvotes: 0

Views: 414

Answers (2)

Christian
Christian

Reputation: 28125

The very basic thing any programmer must learn is the meaning of context.

What am I going about here? If you knew the meaning of context, you wouldn't have asked this question. Now that (I hope) you know, you won't ask how to show <test> as HTML, or how to pass a variable to javascript.

So what's it all about? It's really easy. Context is the simple fact that something in a system may mean something entirely different somewhere else.

For example, in your case, a PHP string may mean something entirely different to MySQL. You can't just pass the string and expect everything to run smoothly - it won't. So, now that you know what context means, you need to know something else that is important. You always need to convert a value from the older context to the newer one. Always.

Again, in your case, it's mysql_real_escape_string(), but a word of warning; conversion functions are context specific, so, for example, you can't use mysql_real_escape_string() to pass a string from PHP to Javascript. Similarly, you can't just use addslashes() and expect it to work. In fact, I'd argue that addslashes() is a completely useless and misleading function. Do NOT use it unless you are very sure of what you are doing.

Upvotes: 2

Quentin
Quentin

Reputation: 943214

Should I save varchar field escaped ?

No. You should escape data so that characters (in the data) with special meaning in SQL won't cause you problems.

Once it passes through SQL and gets stored in the database, it won't be escaped any longer.

i'm using now mysql_real_escape_string() for avoiding string-injection.

Don't do that, instead use prepared statements and parameterized queries

I was using addslashes and stripslashes

addslashes is a basic form of escaping. It is pointless unless you know exactly what the target of the data is. You should use something more specific where such a thing exists (and you are – mysql_real_escape_string)

stripslashes does the opposite of addslashes. Using them together is utterly pointless.

Upvotes: 1

Related Questions