How to secure MySQL from SQL Injection?

Question

Possible Duplicate:
Best way to prevent SQL Injection in PHP

This is my SQL Query and I want to make it SQL injection free

 $q1="insert into ecat_user(login_id,password,fullname,gender,disp_name,dob,";
 $q1.="street_addr,city,country,zip,email,aol,msn,icq,yahoo,homepg_link,homepg_caption,description,";
 $q1.="legal_guardian,phoneno,promotioncode,user_type,height,weight,chest,waist,inseam,eyecolor,haircolor,";
 $q1.="shoesize,biceps,collar,experience,travel,notes,added_on,updated_on) values('$modelName','$password','$firstName',";
 $q1.="'$gender','$description','$dob','$streetAddress','$city','$country','$zipCode','$emailAddress',";
 $q1.="'$aolID','$msnID','$icqID','$msnID','$homePage','$homePageCaption','$description','$legalGuardian',";
 $q1.="'$phoneNumber','$promotionalCode','','','','','','','','','','','','','','','','')";
 mysql_query($q1, $Conn);
 $id = mysql_insert_id();

Please suggest.

[http://php.net/mysqli](http://php.net/mysqli) specifically [prepared statements](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) to start off with. — Burhan Khalid, Aug 23 '12 at 12:21
Please, don't use `mysql_*` functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://goo.gl/KJveJ). See the [**red box**](http://goo.gl/GPmFd)? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide, [this article](http://goo.gl/3gqF9) will help to choose. If you care to learn, [here is a good PDO tutorial](http://goo.gl/vFWnC). — PeeHaa, Aug 23 '12 at 12:22
see http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php — Harshal, Aug 23 '12 at 12:24

rid · Answer 1 · 2012-08-23T12:25:38.860

6

Stop using the ancient, soon to be deprecated mysql_* functions, and start using PDO instead. Take a look at the documentation for PDO::prepare() to see an example.

Also take a look at the manual page about choosing an API, which states:

It is recommended to use either the mysqli or PDO_MySQL extensions. It is not recommended to use the old mysql extension for new development.

edited Aug 23 '12 at 12:25

answered Aug 23 '12 at 12:19

rid

61,078
31
152
193

score 0 · Answer 2 · answered Aug 23 '12 at 12:20

It really depends on how you are getting your variables.

The best and safest way is to use prepared statements. You can do these with mysql_* statements, but using PDO is probably a better option again.

This is an esample of using prepared statements in PDO:

<?php
/* Execute a prepared statement by passing an array of values */
$sql = 'SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $sth->fetchAll();
$sth->execute(array(':calories' => 175, ':colour' => 'yellow'));
$yellow = $sth->fetchAll();
?>

The idea is you prepare the statement - that tells the database what it should expect, then you pass the arguments to it with the array during the execute statement. anything funny is taken right out of the equation.

score 0 · Answer 3 · answered Aug 23 '12 at 12:20

0

Replace all values with placeholders and prepare the statment before sending it. For instance with PDO.

answered Aug 23 '12 at 12:20

Clarence

2,944
18
16

score 0 · Answer 4 · edited May 23 '17 at 10:30

You've got two options - Escaping the special characters in your unsafe_variable, or using a parameterized query. Both would protect you from SQL Injection. The parameterized query is considered better practice, but escaping characters in your variable will require fewer changes.

This is clearly described and cleared in the above article, please go through it compeletly.

How can I prevent SQL injection in PHP?

How to secure MySQL from SQL Injection?

4 Answers4