0

I've been looking around a bit on different ways of inserting data into a database table, and I am not sure which one is the right/best/most secure way of doing it.

I have a form input in which a user can enter some data. I have the variables:

$name = "Steve";, $password = "abc123";, $ip = "1.1.1.1"; and $admin = 0;

The way I currently insert this data into the table is as following:

$q = "INSERT INTO users (username, password, ip, admin) VALUES ('$name', '$password', '$ip', '$admin')";
$query = $db->prepare($q);
$result = $query->execute();

What improvements would you make? And why? I've seen a few put :name instead of $name. But when I did that, it literally inserted ":name" into the database, and not the actual name of the person.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
TyhaiMahy
  • 85
  • 1
  • 2
  • 8
  • You should bind variables to [prevent SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Jay Blanchard Jul 07 '15 at 16:19

2 Answers2

1

You may use the code listed below:

$stmt = $db->prepare("INSERT INTO table(field1,field2,field3,field4,field5) VALUES(:field1,:field2,:field3,:field4,:field5)");
$stmt->execute(array(':field1' => $field1, ':field2' => $field2, ':field3' => $field3, ':field4' => $field4, ':field5' => $field5));

Full PDO reference

varunsinghal
  • 329
  • 4
  • 12
1

I don't think that's quite right still. You do want to use the :name method. The reason it inserted that literally was because you missed something on the execute, the array that tells it what values to bind to what parameters.

It should be:

$q = "INSERT INTO users (username, password, ip, admin) VALUES (:name, :password, :ip, :admin)";
$query = $db->prepare($q);
$result = $query->execute(array(":name" => $name, ":password" => $password, ":ip" => $ip, ":admin" => $admin));

As for why: It will let them handle the correct binding and sanitizing of parameters, regardless of their type.

mGuv
  • 621
  • 1
  • 4
  • 12
  • Would this apply to anything like SELECT and UPDATE as well? And always make an array like `":name" => $name` – TyhaiMahy Jul 07 '15 at 16:36
  • For example: `$sql = "UPDATE people SET country = '$country_id' WHERE name = '$name'";` should be `$sql = "UPDATE people SET country = ':country_id' WHERE name = ':name'";` – TyhaiMahy Jul 07 '15 at 16:38
  • @TyhaiMahy, yes, all your statements should use it. It's much safer. Anywhere you want to use a variable, you should use the `:name` and `array(":name" => $name)` method. You shouldn't manually wrap them in quotes though, that will be handled for you when it detects it is a string. – mGuv Jul 08 '15 at 08:13