Which is the Best way to prvent SQL injection using PHP

Question

Now my query, SP is breaking if i enter

ssds ' " ' sdsds

or just

' " '

this is for mainly search functionality.

Which will be the best way to avoid all possibilities.

eg: str_replace or better ways.. write some function!

See http://stackoverflow.com/questions/216324/avoiding-sql-injection-in-a-user-generated-sql-regex — Spencer Hakim, Jan 04 '11 at 20:14

score 4 · Accepted Answer · answered Jan 04 '11 at 20:15

4

You didn't mention which DBMS, so I'm assuming MySQL here.

The best way would be to use PDO and/or prepared statements. The next best way would be to use mysql_real_escape_string() if you are using the procedural API.

answered Jan 04 '11 at 20:15

Eric Petroelje

59,820
9
127
177

Can i use mysql_real_escape_string for any other database like db2? just for filtering – zod Jan 04 '11 at 20:24
@zod - no, mysql_real_escape_string requires a connection to mysql to work. There is another function, mysql_escape_string, that will work without a connection, but it's not as good as the "real" version, and I'm also not sure if it will escape the string properly for databases other than MySQL. – Eric Petroelje Jan 04 '11 at 20:29
@zod: No. `mysql_real_escape_string` works only for MySQL. DB2 has `db2_escape_string`. In fact, each database engine provides its own escaping mechanism. PDO, on the other hand, escapes automatically no matter what. – netcoder Jan 04 '11 at 20:29
Thanks Eric and netcoder for the information. Can we use prepared statements in Stored Procedure? Can you send any useful URLS which explains how this can be done? i will look into PDO. IS the PDO and PEAR are same or different? – zod Jan 04 '11 at 20:39
@zod - here would be a good place to start: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.php.doc/doc/t0023502.htm – Eric Petroelje Jan 04 '11 at 21:10

score 1 · Answer 2 · edited Jan 04 '11 at 20:20

1

Check out prepared statements in the MySQL manual, in the PHP manual, or this article, before even starting to look at escaping.

It is just too easy to get escapes wrong!

Dw.

edited Jan 04 '11 at 20:20

netcoder

66,435
19
125
142

answered Jan 04 '11 at 20:16

Dirk-Willem van Gulik

7,566
2
35
40

score 1 · Answer 3 · answered Jan 04 '11 at 20:18

Use a prepared statement to separate your logic (SQL) and your content (whatever your input is). You don't have to worry about all those escape things, you just tell your query that you're input is a string, not SQL code. Guessing mysql:

http://www.php.net/manual/en/mysqli-stmt.prepare.php

quick example:

$stmt->prepare("INSERT INTO table VALUES (?)");
$stmt->bind_param("s", "your'''\\\input goes here");
$stmt->execute();

Which is the Best way to prvent SQL injection using PHP

3 Answers3