PHP Protect query from mysql Injection.

Question

How can I add mysql_real_escape_string() to this:::

$result = mysql_send("INSERT customers SET user='$username', pword='$pass1', 
                      firstname='$firstname', lastname='$lastname', email='$email', 
                      active='No', activecode='$activecode', dateofbirth='$dateofbirth', 
                      gender='$gender', title='$title', occupation='$occupation', 
                      address='$address', city='$city', country='$country', zip='$zip',
                      mobile='$mobile', telephone='$telephone', fax='$fax', 
                      website='$website'
                     ");

Answer 1

$result = mysql_send("  INSERT  customers
                        SET     user='".mysql_real_escape_string($username)."', 
                                pword='".mysql_real_escape_string($pass1)."', 
                                firstname='".mysql_real_escape_string($firstname)."', 
                                lastname='".mysql_real_escape_string($lastname)."', 
                                email='".mysql_real_escape_string($email)."', 
                                active='No', 
                                activecode='".mysql_real_escape_string($activecode)."', 
                                dateofbirth='".mysql_real_escape_string($dateofbirth)."', 
                                gender='".mysql_real_escape_string($gender)."', 
                                title='".mysql_real_escape_string($title)."', 
                                occupation='".mysql_real_escape_string($occupation)."', 
                                address='".mysql_real_escape_string($address)."', 
                                city='".mysql_real_escape_string($city)."', 
                                country='".mysql_real_escape_string($country)."', 
                                zip='".mysql_real_escape_string($zip)."', 
                                mobile='".mysql_real_escape_string($mobile)."', 
                                telephone='".mysql_real_escape_string($telephone)."', 
                                fax='".mysql_real_escape_string($fax)."', 
                                website='".mysql_real_escape_string($website)."'
                    ");

Answer 2

I make it this way (assuming HTML form's field names exactly match a database field name):

$fields = explode(" ","user pword firstname lastname email ative activecode dateofbirth gender title occupation address city country zip mobile telephone fax website");

$_POST['active'] = "Mo"; // I know it's kinda dirty but it works. 
$sql = "INSERT INTO customers SET ".makeDdbSet($fields);

function makeDdbSet($fields) {
  $q='';
  foreach ($fields as $v) $q.="`$v` = '".mysql_real_escape_string($_POST[$v])."', ";
  return trim($q,", ");
}

looks neat to me.

Answer 3

Maybe you can take some time and check out Doctrine ORM.

Saving to database would then look like:

$customer = new Customer();
$customer->fromArray($data); // $data = array("firstname"=>"John", ...)
$customer->save();

Everything will be escaped, your program will also be more readable ...

Answer 4

Escaping is quite old-school. Instead, use prepared statements to separate queries and data.

This saves you lots of headaches.

$sql = "INSERT customers SET user=:user, pword = :pword .....";
$sth = $dbh->prepare($sql);
$sth->execute(array('user => $username, 'pword' => $password));

Depending on where you get the data from, you might also directly have it in an array.

For example, in case you get a lot of data from a form, with the variable names pword, user and so on you can directly use that array

$sth->execute($_POST);

Answer 5

$result = mysql_send("INSERT customers SET user='$username', pword='$pass1', firstname='".mysql_real_escape_string($firstname)."', lastname='".mysql_real_escape_string($lastname)."', email='".mysql_real_escape_string($email)."', active='No', activecode='".mysql_real_escape_string($activecode)."', dateofbirth='".mysql_real_escape_string($dateofbirth)."', gender='".mysql_real_escape_string($gender)."', title='".mysql_real_escape_string($title)."', occupation='".mysql_real_escape_string($occupation)."', address='".mysql_real_escape_string($address)."', city='".mysql_real_escape_string($city)."', country='".mysql_real_escape_string($country)."', zip='".mysql_real_escape_string($zip)."', mobile='".mysql_real_escape_string($mobile)."', telephone='".mysql_real_escape_string($telephone)."', fax='".mysql_real_escape_string($fax)."', website='".mysql_real_escape_string($website)."'");