SQL Injection

[devianleong]

Hi, I'm new to PHP and I want to know how to prevent SQL injection? Any php features can prevent the SQL injection?

[rakeshecw]

You can simply prevent SI by using Standard method & function, There are some Rules you have to follow.

• Never Trust on User data.
• Use server side validation.
• Always use salt + MD5 passwords
• Close you database connection at at end of script
• trim user Input data
• Remove special Character’s before insert into your database.

[ryantroop]

Adding/improving on what is above...

Learn SQL and understand what an SQL injection attack is.... if you know the language, you will understand what you don't want users putting into your SQL table, and what they will be getting out of it. Once you know all of that, you can use PHP to remove those threats before they are ever passed into your SQL query.

Learning at minimum to use mysql_real_escape_string() on your user variables will help a lot. It seems that PHP is moving more towards mysqli prepared statements, or PDO.

So, in response to your question... preg_match, mysql_real_escape_string, str_replace and a few others that will prepare your queries to keep them clean are what you will be using.

[Biiim]

Always use salt + MD5 passwords

sha is more secure than md5 - http://www.php.net/manual/en/function.sha1.php

Also limit the privileges on the mysql user the site runs off - if the site doesnt need the drop command to function - don't allow it to

I generally just remove/canonicalise single quotes, double quotes and back slashes. Then limit the variable to be what you expect it to be, for a digit - validate it is just a digit and same for alpha numeric with the ctype functions-> http://php.net/manual/en/book.ctype.php

Another trick i learn't is for strings such as an ip address remove the allowed characters (what you'd expect to be in it eg. a full stop) and then validate the remaining string is a digit - got no chance to do any damage through that - usually means you find characters later on that should be allowed through so you have to update it such as an underscore in an email but atleast it's safe.

Simplicity of it is not doing anything silly like delete from products where id LIKE '{$_GET['id']}' Where a user could set id as "%" - ctype_digit would stop that.

When you write something just think how could i do something destructive or unexpected with that script and make it so you can't. Which is what i do, should probably read up on some mysql to realise better what things you could do so you can avoid leaving security holes like in my above example:
delete from products where id LIKE '%'
or

delete from products where id LIKE '' or '' = ''

[veedeoo]

In practice, holes and vulnerabilities of php script becomes probable the moment you type something between the tags below. Before you ventured in writing codes, keep this in mind at all times.. php has a bad twin brother and is equally powerful as php, and this brother's job is to hack anything written in PHP.

<?php   
## All codes below this line, you must protect at all cost. It is your responsibility, and NOT your users..
?>

These vulnerabilities will be escalating in uprecedented rate, when you are not careful. You cannot just let your guards down, whenever you are typing on your code editor. YOu must test everything, before making it available for public access.

PHP is almost synonym to vulnerabilities as windows is almost synonyms to trojans and viruses. However, due to careful programming designs and more attention to detail in security issues, windows users still exist and continue to grow exponentially on a daily basis. The same careful steps and attention to detail in security can be applied, while you write your php program.

Vulnerabilities in php are mostly created by the programmers themselves, but on the other hand we cannot put the blame on them 100%, because it is not easy to protect your codes as you write them. System administrators are also have the sacred responsibilities on updating the server's php version and many other things needed to run a more security friendly servers.

pHP have built-in validate filters and sanitize filters most coders forget from time to time including myself. These filters are working really good and reliable. Some people will disagree with me on this one. However, it is how the script are prepared and laid out prior to use of these filters.

Sample usage of some of this these filters... I should finished this class and then contribute it on the snippet section of daniweb.com

function valitize_String($string){
## sanitize test string this should return true or false
return(filter_var($string, FILTER_SANITIZE_STRING)? true : false);
}

function valitize_Email($email){
## this should validate sanitize email addresses and return true or false
$filteredEmail = filter_var($email, FILTER_SANITIZE_EMAIL);
      return(filter_var($filteredEmail, FILTER_VALIDATE_EMAIL)? true : false);

}

function valitize_Url($url){
## validate and sanitize url and return true or false
$filteredUrl = filter_var($url, FILTER_SANITIZE_URL);
      return(filter_var($filteredUrl, FILTER_VALIDATE_URL)? true : false);
}

How to use in simple environment? You can always apply a pre-validation before using php filters.

$comment = valitize_String($_POST['comment']);
## do anything when things validate as true
$email = valitize_Email($_POST['email]);
$url = valitize_Url($_POST['url']);

There you have it... the rest like password and other things refer to Biiim's response.