0

I'm using mysql_real_escape_string() on every INSERT before anyone asks. However, I want users to be able to type apostrophes but they come up as back slashes. However, I also want them to be able to use backslashes.

The users are trusted but is it possible to allow these characters whilst also preventing the possibility of an SQL injection?

Michael Irigoyen
  • 22,513
  • 17
  • 89
  • 131
DorianHuxley
  • 642
  • 4
  • 10
  • 22

2 Answers2

2

Yes, just use parametrized queries.

Check out this answer

The basic concept is that your search query is compiled before the parameters are added, making SQL injection impossible.

Community
  • 1
  • 1
Sylverdrag
  • 8,898
  • 5
  • 37
  • 54
  • 1
    This should be a "Close as duplicate of" vote rather than an answer. – Álvaro González Aug 22 '13 at 12:56
  • 1
    @ÁlvaroG.Vicario 1. The answer is the same but the question is different. The OP thought his problem was "how to allow apostrophes" - which is probably why his search for a solution failed. Simply closing as duplicate is somewhat unhelpful because he does not realize the concept of "allowing" is flawed and until he does, the other question seems off the mark. 2. Either way, I am still 200 points short from the rep needed to mark questions as dupes, so an answer is the best I can do here. – Sylverdrag Aug 22 '13 at 13:29
  • I'm not asking how to prevent SQL injections. I'm asking how to "get around" the fact that apostrophes become backslashes etc and to display these characters **without** allowing SQL injections – DorianHuxley Aug 22 '13 at 14:35
  • @DorianHuxley That's the problem. There is NO valid reason to be messing around with mysql_real_escape_string. It's not really safe, and you will have endless problems with character replacement. The REAL solution to your problem is to do things properly, using parametrized queries, where safety no longer depends on escaping the user's input. This in turn allows you to use whatever you like, including quotes and apostrophes, without worrying about them being replaced by "safe" characters. – Sylverdrag Aug 22 '13 at 15:41
-2

You can use the function strip_slashes to remove all the slashes that the apostrophes insert.

So you insert the data into the database using mysql_real_escape_string(). Then when you want to pull the data to the front end you run it through the function stripslashes() and it will remove all the slashes.

You should also consider using mysqli as mysql is deprecated.

Source
  • 1,026
  • 4
  • 11
  • 23