A reasonable level of security vs. sql injection?

Question

I'm getting ready to launch a site - the first one that I coded from the ground up. It's going to be low traffic, and low-profile (probably won't get spidered by search engines.) I'm using PEAR's DB library and its query() method's placeholders to store user data, as follows:

<?php
require_once('db.inc');
$firstname = $_POST['firstname']; 
$lastname = $_POST['lastname'];
$rsvp = $_POST['rsvp'];
$mail = $_POST['email'];
$phone = $_POST['phone'];
$lodging = $_POST['lodging'];
$extra = $_POST['extra'];
$msg = $_POST['msg'];
$password = $_POST['password'];
$id = $_POST['id'];
$username = $firstname . ' ' . $lastname;

if (isset($id)) {
  $sql = $conn->query("UPDATE guest SET username = ?, mail = ?, phone = ?, lodging = ?, extra = ?, msg = ?, role = ?, password = ?, mailed = ? WHERE id = ?", array($username, $mail, $phone, $lodging,$extra, $msg, 2, $password, 0, $id)); //TODO!! set mailed to 1 in production
  } else {
  $sql = $conn->query('INSERT INTO guest (username, password, rsvpstatus, role, mail, phone, lodging, extra, msg, mailed)VALUES (?,?,?,?,?,?,?,?)', array($username, $password, $rsvp,  2, $mail, $phone, $lodging, $extra, $msg, 1));
  }

 header('location:main.php');

Does this seem like a reasonable level of protection vs sql injection?

I'd always run into problems when I inserted a new token higher in the sql query text and bump the order of the parameters around, ended up writing my own function that would use the index on the param order to replace tokens like {0},{1},{2} etc in the sql query, it also permitted me to have multiple of the same token around my query — Scuzzy
– Scuzzy, Commented May 26, 2011 at 2:48

Ted Hopp · Accepted Answer · 2011-05-26 02:38:05Z

4

Placeholders and binding like you are doing are exactly the defense against sql injection. As long as you never directly interpolate any user input into the sql, you're fine.

answered May 26, 2011 at 2:38

Ted Hopp

235k48 gold badges412 silver badges533 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Simon Over a year ago

+1, prepared statements are 100% injection proof if they're used for every query on a site.

Ignacio Ocampo · Accepted Answer · 2011-05-26 03:04:28Z

1

You can use the mysql_real_escape_string function in PHP (view function in php.net)

Escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query()

Example:

$secure_firstname = mysql_real_escape_string( $_POST['firstname'] );
$secure_lastname = mysql_real_escape_string( $_POST['lastname'] );

answered May 26, 2011 at 3:04

Ignacio Ocampo

2,7231 gold badge22 silver badges31 bronze badges

Comments

alex · Accepted Answer · 2011-05-26 02:35:24Z

0

So long as that library quotes and escapes all of its arguments, it will be fine.

answered May 26, 2011 at 2:35

alex

492k205 gold badges890 silver badges991 bronze badges

Collectives™ on Stack Overflow

A reasonable level of security vs. sql injection?

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related