Avoid MySQL Injection replacing single and double quotes

Question

to sanitize user input we usually use mysql_real_escape_string, but for example if i want to escape: O'Brian it will return O\'Brian and I don't really like this because if i want to print this name, I have to strip slasheseverytime. So I thought to use this function:

$search = array("'", '"');
$replace = array("´", "“");
$str_clean = str_replace($search, $replace, "O'Brian");

Is this simple function protecting me from MySQL-Injection? Thank very much and sorry for my bad English.

Chk this It will help you Up:http://stackoverflow.com/questions/139199/can-i-protect-against-sql-injection-by-escaping-single-quote-and-surrounding-use — OM The Eternity, Mar 23 '12 at 10:03

score 3 · Answer 1 · answered Mar 23 '12 at 10:00

3

No - no more escaping!

Use mysqli or PDO and prepared statements

http://php.net/manual/en/mysqli.prepare.php

http://php.net/manual/en/pdo.prepared-statements.php

answered Mar 23 '12 at 10:00

scibuff

13,377
2
27
30

Sorry but I don't want to use an object oriented style :( – Simone Verzino Mar 23 '12 at 10:06
mysqli has non-OOP options as well – scibuff Mar 23 '12 at 10:47

score 2 · Answer 2 · answered Mar 23 '12 at 10:00

2

mysql_real_escape_string does add the \ for string escaping only and does not add them to the database, so you don't have to use stripslashes while displaying the content.

If you are really getting the \ stored in the database, do off the magic_quote

answered Mar 23 '12 at 10:00

Shakti Singh

84,385
21
134
153

magic_quote is off ;) So I have to use mysql_real_escape_string only for SELECT query and not also for UPDATE or INSERT, right? – Simone Verzino Mar 23 '12 at 10:10
@SimoneVerzino: You need it where you needs to escape strings for mysql. – Shakti Singh Mar 23 '12 at 10:18

score 2 · Answer 3 · answered Mar 23 '12 at 10:00

2

You should always use mysql_real_escape_string to escape input. This is for when you're writing values into your database, not when you're reading values from your database. When you write "O\'Brian" to your database, it's stored as "O'Brian", and when you read it back out, you should also get "O'Brian" (and won't need to strip the slashes, since they don't exist).

answered Mar 23 '12 at 10:00

Waynn Lue

11,344
8
51
76

OK, but if I have to do a SELECT FROM USERS WHERE name = 'O'Brian' I must escape it, and with my function I will solve the problem? – Simone Verzino Mar 23 '12 at 10:14
Yeah, you'd have to escape the variable there as well. So something like `$user = mysql_real_escape_string($user); mysql_query("SELECT * FROM USERS WHERE name = '$user'");` – Waynn Lue Mar 23 '12 at 10:30

score 1 · Accepted Answer · answered Mar 23 '12 at 10:01

1

Yes, obviously it's protecting from SQL Injection attacks

It Escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query().

answered Mar 23 '12 at 10:01

1

I always use UTF-8, so I am protected also from hex strings like 0x27, etc.? – Simone Verzino Mar 23 '12 at 10:18

Avoid MySQL Injection replacing single and double quotes

4 Answers4