Saving in database with mysql_real_escape_string or without it

I’ve heard that I should use “mysql_real_escape_string” when I get postData which is received from users.
So I tried to add the following code in an action page.

$postContents=trim(mysql_real_escape_string($_POST[‘contents’]));

however, if the value of the contents is “Tom’s book”,
$postContents will be “Tom's book.”

Should I save it in my dataBase as “Tom's book”?
or as “Tom’s book” after I remove ""?

You should really be moving away from the mysql_* extension as it’s deprecated as of version 5.5 of PHP. You should migrate over to using PDO where you should use prepared statements to protect against sql injection attacks.

Before you let data near the database you should also sanitize it, a few examples:

  • If you’re expecting a field to be an integer, check that the user submitted data is in inter
    If you’re expecting a string to be longer then x but shorter then y, check that the string length falls within that range
    If you expect a string to only contain certain characters, use a regex to check that it does (regex is a chunky subject in its own right)
3 Likes

@SpacePhoenix is right. If you had a big problem with existing mysql_ already being used, we could get you fixed up and just make you aware of the fact that there’s a better method out there, instead of just pushing PDO at you, which doesn’t solve your immediate problem.

If you’re just beginning to work on sanitation, though, which it seems like you are in this case, please investigate PDO and use a better, more current, and actually secure method. Don’t waste your time learning outdated methods. It’ll benefit you a lot in the long run, and you’ll be learning something pretty useful too!

It should be noted that the primary reason PDO can be more secure is that the query and the data can be (and should be) sent to the database separately. PDO is no more secure than the base mysql library if you embed the data in the query in one call.

Separating the data from the SQL has nothing to do with security - security should have been dealt with long before the data gets anywhere near the database - it simply eliminates the possibility of data being misinterpreted as SQL.

Security is handled by the validation/sanitization of the data when it is first input before it is moved out of the tainted superglobal fields it is received in into regular fields.

Security is handled by the validation/sanitization of the data when it
is first input before it is moved out of the tainted superglobal fields
it is received in into regular fields.

Yes, but that has nothing to do with why PDO can be more secure than the older mysql extension it replaces. Regardless of the library in use to do the insertions validation should be performed. I wasn’t talking about that, nor was I advocating relying solely on PDO’s prepare mechanism for validation.

Quit pointing out very basic things like this in an attempt to gainsay other speakers and make them look foolish, it’s become quite tiresome. Pay more attention to the context of the statement made and the conversation as a whole.

If it is so basic then why do almost all of the people asking these questions not have any validation/sanitisation in any code they post when asking these questions. Obviously these questions would not be asked if the person already had this basic security in place.

Yes it is extremely basic but 99% of the people asking these questions have no knowledge of the basics. If they did then they wouldn’t be asking about output escaping functions and talking about security at the same time.

And look! Here I am, asking you guys to be nice to each other because it would be annoying for the OP if I had to close the thread. :wink:

1 Like

It is true that you should NOT be using deprecated mysql_ functions - at least not on any live site.
But is also true that most (old) examples you’ll find online are those using them.
So if you are learning basics I see little problem using them as long as you understand that at some point - and the sooner the better - you will need to stop using them. (and rewrite your code that used them)

And it is also true that you should validate and sanitize all user supplied input before it gets near the database. IMHO that many seem to miss this is in part a result of the PHP documentation examples not showing the complete steps that should be followed and present a “trimmed down” example of only the “piece” that is being covered by that page of documentation.

As for how to deal with quotation marks being stored into a database, there’s no problem saving something like
Paul O\'b has said \"I love CSS\" on more than one occasion.
in a database.

And you shouldn’t need to worry about the backslash escape character if you’re using it only for HTML output. Browsers understand it as an escape character too,

I’ll grant you that.

Maybe I should stay away for a day or two. Had a root canal this morning and it hasn’t helped my mood. Oooowww.

Root canals suck! And you definitely don’t have to stay away.

I won’t stay away unless the pain makes me trend to being uncivil. Y’all deserve better than that. I’m feeling better today.

2 Likes

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.