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'
                     ");

dont, just call a stored proc :P

Jon Black
– Jon Black

2010-08-30 12:42:29 +00:00
Commented Aug 30, 2010 at 12:42 — Jon Black
– Jon Black, Commented Aug 30, 2010 at 12:42

2ndkauboy · Accepted Answer · 2010-08-30 12:30:45Z

$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)."'
                    ");

Your Common Sense · Accepted Answer · 2010-08-30 12:40:47Z

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.

edited Aug 30, 2010 at 12:40

answered Aug 30, 2010 at 10:22

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

2 Comments

2ndkauboy Over a year ago

A really nice and handy function! But I would have added all lines in the foreach to an ARRAY $q and than used the implode() function and not trimming the last comma.

Your Common Sense Over a year ago

@Kau that's perfectionism that spoils you. there is not a single reason to use array here. Same amount of code and other differences are negligible

knagode · Accepted Answer · 2010-08-30 12:56:31Z

2

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 ...

answered Aug 30, 2010 at 12:56

knagode

5,9735 gold badges53 silver badges73 bronze badges

7 Comments

Your Common Sense Over a year ago

from where do you get that "John"?

knagode Over a year ago

The simpliest example would probably be: $customer->fromArray($_POST); --> every field from POST which matches column in "customer table" will be saved into database.

Your Common Sense Over a year ago

well with actual data it will be way more code than now. what's the benefit?

knagode Over a year ago

Less code doesn't mean better program. Doctrine simply makes your program more readable, it fasten your development and give you much more power than SQL. You can check it out here: doctrine-project.org (ORM section).

Your Common Sense Over a year ago

Why should I check out somewhere? Why can't you show that more readable code right here? Is it too hard to do it using your ORM?

|

Alex · Accepted Answer · 2010-09-02 06:48:26Z

2

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);

edited Sep 2, 2010 at 6:48

answered Aug 30, 2010 at 12:47

Alex

35.7k20 gold badges115 silver badges202 bronze badges

2 Comments

Your Common Sense Over a year ago

it's twice more code than current approach. Any way to make it shorter?

knagode Over a year ago

You could create a function which would generate SQL and prepared data array for you. (eg: function insert_into($table, $data) )

Maulik Vora · Accepted Answer · 2010-08-30 10:15:24Z

$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)."'");

Collectives™ on Stack Overflow

PHP Protect query from mysql Injection.

5 Answers 5

Comments

2 Comments

7 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

7 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related