2

I am totally confused, I've read a few posts but still I am not sure. I do not want to go the prepared statements route since this site is only on our intranet. I've read the following posts, but still I am uncertain.

  1. When to use mysql_real_escape_string?
  2. When to use mysql_real_escape_string()

My question: Should I use mysql-real-escape-string

  1. When only I get user input from a form OR
  2. On all my queries? eg: SELECT * FROM ......

For example in this post it states: You need to call this function when building SQL queries with string literals. You should not call it anywhere else.

Community
  • 1
  • 1
Wilest
  • 1,820
  • 7
  • 36
  • 62
  • this should be a comment on a post. – Daniel A. White Mar 16 '12 at 12:22
  • possible duplicate of [When to use mysql_real_escape_string?](http://stackoverflow.com/questions/5278043/when-to-use-mysql-real-escape-string) – Daniel A. White Mar 16 '12 at 12:22
  • 2
    Using prepared statements is not actually harder or more time-consuming than using the "old" method, very often quite the contrary. – JJJ Mar 16 '12 at 12:22
  • 3
    I would treat any software for the intranet as though it was the internet - simple because you do sometimes get discruntled employess or employess that make mistakes. – Ed Heal Mar 16 '12 at 12:24
  • You really should reconsider prepared statements (PDO or Mysqli). MySQL has been superceded by these better alternatives, and it's a good habit to start using them even for a local intranet application because you should always be using the more up-to-date libraries in the real world. – Mark Baker Mar 16 '12 at 12:33
  • @DanielA.White the accepted answer in the linked question is wrong. I doubt that linking such questions will do any good, but harm. – Your Common Sense Mar 16 '12 at 12:48
  • Answer: never. [Don't use `mysql_*`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1) at all; the `mysql_*` functions are outdated, [deprecated](http://us3.php.net/manual/en/intro.mysql.php), and insecure. Use [`MySQLi`](http://us3.php.net/manual/en/book.mysqli.php) or [`PDO`](http://us3.php.net/manual/en/intro.pdo.php) instead, and use prepared statements, not `mysqli_real_escape_string()`. – elixenide Feb 21 '14 at 06:08

5 Answers5

2

Well I actually use it on :

ANY variable that I'm going to put into a MySQL Query and which could have been modified by a user, either if it's direct user input (through a form), or a parameter that I've been passing around via GET requests, etc

You get my point... :-)

Dr.Kameleon
  • 22,532
  • 20
  • 115
  • 223
  • try to think what this function actually do and why – Your Common Sense Mar 16 '12 at 12:29
  • I really can't understand your being so... negative towards my approach; and yes, I know what it does... – Dr.Kameleon Mar 16 '12 at 12:33
  • you don't. I am negative not towards your approach but towards upvotes it gets. – Your Common Sense Mar 16 '12 at 12:34
  • anyway, since I don't consider myself a DB security Guru, if you really have something to contribute to this particular topic, I'd be glad to read it... – Dr.Kameleon Mar 16 '12 at 12:36
  • He explained it in a manner I understood, that's why I upvoted it! I am a learning php and thus asking questions that is Common Sense to you. Stop judging please. – Wilest Mar 16 '12 at 12:37
  • You don't have to be a security guru. mysql_real_escape_string has nothing to do with security at all. – Your Common Sense Mar 16 '12 at 12:38
  • 2
    @your common sense, is SQL code injection a security issue? is mysql_real_escape_string a way to prevent SQL code injection? please, why mysql_real_escape_string has nothing to do with security at all? – Saic Siquot Mar 16 '12 at 13:23
  • 1
    @Luis SQL code injection a security issue but this function has nothing to do with it. because it is string formatting function, not SQL injection prevention function. there is no word "injection" in it's name. it is used to format strings. WHATEVER strings, not only possibly malicious ones. On the other hand, it is completely useless for the any other part of the SQL query. – Your Common Sense Mar 16 '12 at 13:30
  • @your common sense, yes, mysql_real_escape_string is a formatting function to concatenate correctly raw data into sql statments. A must in any case, but mandatory to prevent sql injection. (indirectly?? a security issue??) – Saic Siquot Mar 16 '12 at 13:58
  • @Luis why this function have to be used to deal with injections?Where did you get such an odd idea? what is "raw data" you are talking about? why concatenate? – Your Common Sense Mar 16 '12 at 14:06
2

What prepared statements do (among other things) is to call a method similar to mysql_real_escape_string()
If you don't use PDO, what is fine, you must understand what are you doing, and you will get the same security level.
The only and simple rule is all raw data needs mysql_real_escape_string() (or similar from other languages)
Examples are:
- Data from user input
- Data that you have stored RAW on DB (witch is the best way) and you are using on a new sql statment
- Data from unknow/other origin
The detalis are:
- be sure to not apply twice (to preserve data correctly)

Saic Siquot
  • 6,513
  • 5
  • 34
  • 56
  • 1
    Would you delete it if I show you the simple code which, despite of following your answer instructions, will allow an injection? – Your Common Sense Mar 16 '12 at 13:31
  • 3
    A very misleading answer. How mysql_real_escape gives more security than pdo? The concept of pdo is that the sql and data are send seperately. People should try to encourage newbies using pdo.... An approach like this is the reason why all php tutorials, books contains vulnerable codes. – itachi Mar 16 '12 at 13:35
  • This is an incorrect answer, as Your Common Sense and itachi have pointed out. [Don't use `mysql_*`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1) at all; the `mysql_*` functions are outdated, [deprecated](http://us3.php.net/manual/en/intro.mysql.php), and insecure. Use [`MySQLi`](http://us3.php.net/manual/en/book.mysqli.php) or [`PDO`](http://us3.php.net/manual/en/intro.pdo.php) instead and use prepared statements, which are much safer than `mysql_real_escape_string()`. – elixenide Feb 21 '14 at 06:10
1

It's important when you don't know for sure what's included in the string. This means user inputs.

You might also want to consider moving to prepared statements with PDO.

BudwiseЯ
  • 1,846
  • 2
  • 16
  • 28
0

You should use it on any variable that you are interpolating in a SQL query.

Everything that is not a literal string should be sanitized. It doesn't matter if you got it from a form, a database or anything else, if it's not constant then you should sanitize it.

Dan Berindei
  • 7,054
  • 3
  • 41
  • 48
-2

The quoted sentence in your question is true.
All the other answers are wrong.

Every time you are going to add a quoted string to the SQL query, you have to always escape it.
That's the only case when you have to use mysql_real_escape_string().

PS. I can't believe this question is still alive with all it's answers.

Stackoverflow is a very strange place.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • OK so if I understand corect: 1) input from a user form = yes. 2) Select * from usrs WHERE username = '$user' = not necessary because I already escaped the user input. 3) .. while($row = mysql_fetch_assoc($result)) { $userid=$row['id']; $departmentid=$row['dept']; } = not necessary because it is info from the table? 4) $str = "Nike's ...." = yes – Wilest Mar 16 '12 at 13:17
  • There is no words "input" or "user" in my answer. So, you understood it wrong. – Your Common Sense Mar 16 '12 at 13:20
  • only $name in your case, as you are inserting it in the SQL query in quotes - so, SQL treats it as a string. mysql_real_escape_string does format your data to be suitable as SQL string. – Your Common Sense Mar 16 '12 at 13:27
  • 4
    You are splitting hairs here. How a programmer priorly can know when the user will quote and when he won't? – itachi Mar 16 '12 at 14:17
  • @itachi to whom you are addressing? what exactly looks like splitting hairs to you? – Your Common Sense Mar 16 '12 at 14:23