0

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

Adrian Tanase
  • 671
  • 8
  • 20
  • 2
    Use prepared statements with mysqli, then you don't have to worry about quotes. http://www.php.net/manual/en/intro.mysqli.php – Timm Jun 03 '12 at 20:27
  • 1
    I think (I hope) the advice to save *unescaped* data in your database was referring to html encoding. Lots of sites will use the php function `htmlspecialchars()` to *escape* user-supplied strings before output, to avoid XSS. Sometimes it's tempting to do this before inserting stuff into your database, but it's best to do it on display instead. – grossvogel Jun 03 '12 at 20:28
  • 1
    **Don't use mysql_* functions.** Instead, use PDO or MySQLi (which is similar to the older functions). – Christian Jun 03 '12 at 20:38

2 Answers2

2

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.

Christian
  • 27,509
  • 17
  • 111
  • 155
1

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.

Community
  • 1
  • 1
Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
  • well for me when i do mysql_real_escape_string($_POST['comment_body']) it will save in the database \'\" for the string "' . it doesn't just escape for the MySQL command, but it also saves escaped. I don't bother since i can display with stripslashes afterwards on the page but is this the way it should work ? maybe the server has something activated that does escaping automatically ? – Adrian Tanase Jun 04 '12 at 10:43
  • Have you checked to see what the raw value of `$_POST['comment_body']` is? It sounds like you are running an out of date version of PHP with [magic quotes](http://php.net/manual/en/security.magicquotes.php) turned on. – Quentin Jun 04 '12 at 10:53
  • yes it seems that on the server that hosts my stuff, magic quotes are turned on. – Adrian Tanase Jun 08 '12 at 08:03
  • so you say that...i'm first escaping for MySQL and then the magic quotes add the slashes that I see in the database ? – Adrian Tanase Jun 08 '12 at 08:04
  • The data comes into PHP. Magic Quotes adds slashes. You then escape for the database. Magic Quotes might add more slashes when you get the data out of the database as well … I've made it a point to not have them turned on, so I'm not sure about that. – Quentin Jun 08 '12 at 08:15