Using prepared statements correctly?

[Fred_Fredburger]

Hi, I'm fairly new to PHP and MySQL, and I'm having a problem. I did some research about protecting against SQL injection, and decided prepared statements seemed to be the best way to go. The problem is, I don't think it's working the way it's supposed to. If I understand right, no matter what I input I shouldn't be able to make MySQL choke because the parameters are separate from the query. However, when I input the ' character, the query doesn't execute, making me think it's still vulnerable in some way. Strangely, using mysql_error() doesn't show anything.

I'm using PHP 4 and MySQL 5.0. I know they're old, but I have no choice in the matter. Am I doing this correctly, or is there a better way to defend against SQL injection? Here's an example of what I'm using

$statement = "PREPARE edit_customer FROM 'UPDATE customers SET customer_name=?, customer_address=?, customer_contact=?, customer_phone=?, customer_email=?, customer_notes=?'";

@mysql_query($statement);

@mysql_query("SET @customer_name = '$customerName'");
@mysql_query("SET @customer_address = '$customerAddress'");
@mysql_query("SET @customer_contact = '$customerContact'");
@mysql_query("SET @customer_phone = '$customerPhone'");
@mysql_query("SET @customer_email = '$customerEmail'");
@mysql_query("SET @customer_notes = '$customerNotes'");

$query = "EXECUTE edit_customer USING @customer_name, @customer_address, @customer_contact, @customer_phone, @customer_email, @customer_notes";

$result = @mysql_query($query);

@mysql_query("DEALLOCATE PREPARE edit_customer");

if ($result) {
  echo "<p>Customer edited.</p>";
} else {
  $errors[] = "Database error.";
  echo "<p>SQL ERROR: " . mysql_error() . "</p>";
}

[Weedpacket]

To answer why it's failing, it's because if there is an apostrophe in $customerName, etc., then the SET commands will fail because they will have bad syntax.

To fix the problem, stop trying to do it all by hand and just use the [man]PDO[/man] interface.

[cahva]

PDO doesnt work with PHP4(atleast to my knowledge) so thats out of picture and I dont think they would even install it if upgrading PHP4 to PHP5 is so hard task 😉 Gladly theres many database abstraction classes that you can use for example PEAR MDB2.

[Weedpacket]

Gaaah, yeah. Stone knives and bearskins it is, then.

[man]mysql_real_escape_string[/man] would safely escape incoming strings for use in MySQL queries. Of course, this can also be used to defend against SQL injection (or just plain faulty input) in ordinary immediately-executed statements as well - making the use of prepared statements redundant.