How can i prevent sql injection but keep " and '?

Question

How do prevent sql injection in php but still show " and '? A the moment I am using

$input = strip_tags($input);
$input = htmlentities($input);

However the output is \" and \'. Is there anyway I can show " and ' without the slashes but keep them there so I don't get injected?

Replace ' with '' (two times single quote), before executing command to database — Waqas Raja, Feb 06 '11 at 15:38

score 5 · Answer 1 · answered Feb 06 '11 at 15:37

5

The method you show is not a proper way to protect against SQL injection!

Always use the sanitation method provided by the database library you are using, e.g. mysql_real_escape_string() if you work with the standard mysql library. The sanitation method will not alter any characters in the end result.

Alternatively, use prepared statements in PDO or mysqli - those do input sanitation automatically if you bind the incoming data correctly.

answered Feb 06 '11 at 15:37

Pekka

442,112
142
972
1,088

Ok thanks, I'll try that. Do I only need to do this for when inserting/deleting things in mysql? – Yesterday Feb 06 '11 at 15:39
1

@Cameron yes, that sanitation method is mysql specific. There are other things you need to do e.g. when outputting data in a HTML page or sending them as E-Mail but this specific thing is for mysql only. – Pekka Feb 06 '11 at 15:40
So, if I want to echo these before I put them into my database is it okay to leave out mysql_real_escape_string untill I insert the data? It is showing \\\' now and \\\" for some reason! – Yesterday Feb 06 '11 at 15:42
do you have to use mysql as a database link for mysql_real_escape_string or can you use odbc links too? Thanks – Drewdin Feb 06 '11 at 15:49
@Cameron you may have magic quotes turned on - check them out and turn them off if necessary @Drewdin ODBC? No, as said, `mysql_real_escape_string` is for the `mysql_*` family of functions only. Every database wrapper has its own sanitation functions if necessary – Pekka Feb 06 '11 at 15:54
I'm not sure if I can, I use a free webhost. How would I do this? – Yesterday Feb 06 '11 at 15:55
@Cameron and http://stackoverflow.com/questions/517008/how-to-turn-off-magic-quotes-on-shared-hosting – Pekka Feb 06 '11 at 15:57
@Pekka, thanks for the info, i figured that but i have an application where im connecting to odbc connection and i wanted to sanitize the input. Any suggestions? – Drewdin Feb 06 '11 at 15:57
@Drewdin what database wrapper are you using? There should be something on sanitation in the manual, and there probably is something on the topic on SO. – Pekka Feb 06 '11 at 16:01
@Pekka Im a rookie so im not sure what a wrapper is, i am connecting to a sybase db on a windows box to query some data. I'll check the manual, thanks! – Drewdin Feb 06 '11 at 17:20
1

@Drewdin "wrapper" means the database library you use to connect to the database. If you're unsure, ask a separate question about it, showing some code - somebody will definitely be able to tell you whether what you do is safe or not. – Pekka Feb 06 '11 at 17:22

score 1 · Answer 2 · answered Feb 06 '11 at 15:36

1

Make use of prepared statements.
http://de2.php.net/manual/en/pdostatement.bindparam.php
OR
http://de2.php.net/manual/en/mysqli-stmt.bind-param.php

answered Feb 06 '11 at 15:36

Dr.Molle

116,463
16
195
201

score 1 · Accepted Answer · answered Feb 06 '11 at 15:38

First, that code is not stripping backslashes, of course they're still there. Use stripslashes() to take out backslashes, but DON'T DO IT. If you see those slashes in the DB, and you HAVE USED mysql_real_escape_string, chances are you have magic_quotes_gpc on, and you're just adding another set of slahses. Remove those auto added first and then apply mysql_real_escape_string, they won't show this way but will still be there and make for a safe use in querying your DB.

I just added this to the htaccess file: php_flag magic_quotes_gpc off — Yesterday, Feb 06 '11 at 16:26

score 0 · Answer 4 · answered Feb 06 '11 at 15:44

0

There is no magic solution for being careless.

Also those slashes alone don't prevent SQL injections. The presence of them indicates another problem, magic_quotes. Magic quotes were a convenience feature in PHP2, never intended as security function. (Well accidentially they were secure around 1997 when databases didn't support multibyte charsets).

Anyway, disable magic_quotes. Use manual escaping (mysql_real_escape_string) or better yet the much more convenient prepared statements with PDO.

If you want to be lazy, disable magic_quotes still. But use $_GET = array_map("mysql_real_escape_string", $_GET); and do the same for $_POST and $_REQUEST at the start of your scripts and after the database connection was established.
And then apply htmlentities(stripslashes($input)) for writing output to ge rid of the extraneous backslashes.

answered Feb 06 '11 at 15:44

mario

144,265
20
237
291

@Cameron: It's a setting in the `php.ini`. You can change it via .htaccess if you follow the `php_value` tip in the comment here http://php.net/manual/de/security.magicquotes.disabling.php – mario Feb 06 '11 at 15:49
I use shared hosting, is it possible? – Yesterday Feb 06 '11 at 15:52
@Cameron: You'll have to test it. It works with mod_php servers. With a FastCGI setup you would have to install or modify a personal `php.ini` – mario Feb 06 '11 at 15:55

How can i prevent sql injection but keep " and '?

4 Answers4