how do i prevent mySql injection

Question

i need to know about how to prevent our database from sql injection, i read many blog and answer on internet but could not justify which way i should prefer or best. just like for mysql:

 mysqli:
mysql_real_escape_string.

or using

Using PDO:

i know if user something inserting in database it becomes vulnerable to SQL injection, and i generally use this code for insertion:

<?php
$con=mysql_connect("localhost","root","");
$db=mysql_select_db("test",$con);
$foo=$_POST['foo'];
$boo=$_POST['boo'];

$insert="insert into table set foo='$foo',boo='$boo'";

so what should i do for prevent my database injection..... any idea would be appreciated highly.. Thanx in advance

This may help you. http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons — curious_coder, May 18 '13 at 13:14
this question has been asked "i cant even count" number of times.. do a quick search on google or better stackoverflow. Use `PDO`. you will get in built security. — Praveen Puglia, May 18 '13 at 13:15

samayo · Accepted Answer · 2013-05-18T13:22:35.460

2

If you know the below code, very well, then translating it to PDO would be like:

FROM SQL Code:

<?php
$con=mysql_connect("localhost","root","");
$db=mysql_select_db("test",$con);
$foo=$_POST['foo'];
$boo=$_POST['boo'];
$insert="insert into table set foo='$foo',boo='$boo'";

TO PDO Code:

<?php
$con= new PDO('mysql:host=localhost; dbname=xxxx', 'username', 'password')
$stmt = $con->prepare('INSERT INTO Table (foo, boo) VALUES (?,?)');
$stmt->execute(array($_POST['foo'], $_POST['boo']))

PDO helps you to prepare the query, then execute unlike mysql which does all together at the same instance.

Now, $con = new PDO() is very much like, mysql_connect() and mysql_select_db() it opens the connection.

$stmt is a variable, which holds the returned query much like the $result in $result = mysql_query() next is execute() this actually executes your code, meaning all your sql queries are prepared then executed, one after the other making sql injection almost impossible, to be.

this is a basic and good tutorial if you want to learn about PDO http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

edited May 18 '13 at 13:22

answered May 18 '13 at 13:16

samayo

16,163
12
91
106

so first i hvae to learn PDO imlementation – Dinesh May 18 '13 at 13:19
@Jack yes, this is a very good tutorial http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers – samayo May 18 '13 at 13:20
@Jack you can also do a safe query in mysql but wont be a good option just because its deprecated you can read this http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php/14110189#14110189 for more information – NullPoiиteя May 18 '13 at 13:23
@NullPointer: So nice Description. Thank you very much – Dinesh May 18 '13 at 13:27

score 1 · Answer 2 · answered May 18 '13 at 13:16

1

You should use PDO for several reasons:

1) It has parameterized querys so no sql injection

2) PHP's mysql functions are depreciated as of PHP 5.5.0

answered May 18 '13 at 13:16

Ace

152
10

Don't worry there is always someone to upvote :) – HamZa May 18 '13 at 13:22
Yeah I know, just it is a perfectly legit answer – Ace May 18 '13 at 13:23
1

we can not say its perfactly legit ... i mean you can atleast tell whats worn in that code ...i know mysql_* is deprecated .. and mysql_* are as safe as pdo or mysqli there is no question about that .. but on the top they are deprecated and that it – NullPoiиteя May 18 '13 at 13:24

score 1 · Answer 3 · answered May 18 '13 at 13:19

1

by using stored procedures instead of direct queries , you may prevent your system from sql injections

answered May 18 '13 at 13:19

Freak

6,786
5
36
54

so you proc up a bunch of mysql_ commands and feed in user supplied data ? – Drew May 18 '13 at 13:57
so whats bad in it? can you explain the reason of your downvote? – Freak May 18 '13 at 14:03
because you didnt change anything .... http://stackoverflow.com/questions/239905/am-i-immune-to-sql-injections-if-i-use-stored-procedures – Drew May 18 '13 at 14:05
so i am going to retype the crux of the reference i provided in this little box ? – Drew May 18 '13 at 14:12
but you should write the relevant part.I am not DB expert but i just share my knowledge as much I know. Anyway its your right to vote down if you think that I am wrong :) goodluck – Freak May 18 '13 at 14:22
if the user-supplied data as parameters are not sanitized you have effectively said this: what you used to call via inline sql, you are passing off to a stored proc to perform exactly like before. nothing has changed, except a false sense of security. – Drew May 18 '13 at 14:25

how do i prevent mySql injection

3 Answers3