Security Tips?

[GerryCarr]

Hi,

 

I made a site about 8 years ago, that's extremely weak to sql injection.

 

I'm now working on a new site, but need some advice on how to make my new site secure.

 

How do I prevent SQL injection? I used addslashes last time but this doesn't seem to of helped.

[KevinM1]

That's because addslashes has never been a proper method of escaping database input.

 

The best way to escape input is to use prepared statements with either MySQLi (notice the 'i' at the end... the old mysql_* functions are deprecated and should not be used), or PDO.  The online PHP manual has examples of both.

[.josh]

use prepared statements.

 

Example insert:

 

$db = new PDO('mysql:host=localhost;dbname=mydb;charset=utf8', 'username','password');
$query = $db->prepare("INSERT INTO table (column) VALUES (:column)");
$query->bindValue(':column', 'some value'), PDO::PARAM_STR);
$query->execute();

Example select:

 

$db = new PDO('mysql:host=localhost;dbname=mydb;charset=utf8', 'username','password');
$query = $db->prepare('SELECT * FROM table where column=:column');
$query->bindValue(':column', 'some value', PDO::PARAM_STR);
$query->execute();
$row = $query->fetch(PDO::FETCH_ASSOC);
echo $row['column'];

This will prevent sql injection. However, you should still have logic in place to validate the user input, for the sake of your script functioning properly. For example, if you expect a user to provide a url or email address or zip code or whatever.. you should validate that it's a proper format so that you can properly do something with it. For example, using the above will prevent sql injection, but letting a user enter in arbitrary value in those fields will not prevent other attacks, such as cross-site scripting attacks. I could enter in your form some javascript and it'll sit in your db and not directly harm your db but if you turn around and output it on some page without validation or escaping, I could make your site output arbitrary js and find other ways to hack your site. So for instance, if you ask for a (US) zipcode, validate that they entered in 5 digits and nothing else, etc..

[boompa]

Read this: Survive the Deep End: PHP Security