using placeholders with php and mysql to prevent i

问题:

I understand mysql_ has been deprecated, but I'm just using it as a tool to learn from a book.

I'm trying to learn about placeholders and I got the following error when I clicked on "add record":

INSERT failed: EXECUTE statement USING @first,@last,@email,@user 
Unknown prepared statement handler (statement) given to EXECUTE

using the following code:

if (isset($_POST['first']) && isset($_POST['last']) && isset($_POST['user_name']) && isset($_POST['email']))
{
    $first      = get_post('first');
    $last       = get_post('last');
    $email      = get_post('email');
    $user_name      = get_post('user_name');

    // begin placeholde code
    $query = 'PREPARE statement FROM "INSERT INTO user_master VALUES(?,?,?,?)"';
    mysql_query($query);

    $query = 'SET @first = "$first",' . 'SET @last = "$last",' . 'SET @email = "$email",' . 'SET @user_name = "$user_name",';
    mysql_query($query);

    $query = 'EXECUTE statement USING @first,@last,@email,@user';
    mysql_query;

    // end placeholder code

    if(!mysql_query($query, $db_server)) echo "INSERT failed: $query <br />" . mysql_error() . "<br /><br />";
}


echo <<<END
<form action = "willingLog.html" method="post"><fieldset><legend>Sign Up:</legend>    <pre>
    First       <input type="text" name="first" />
    Last        <input type="text" name="last" />
    Email       <input type="text" name="email" />
    Username    <input type="text" name="user_name" />
                <input type="submit" value="AD RECORD" />
</pre></fieldset></form>
END;

// also from placeholder code
$query = 'DEALLOCATE PREPARE statement';
mysql_query($query);
// end placeholder code

回答1:

I think your initial PREPARE statement query may be failing, and you should also change the line:

$query = 'SET @first = "$first",' . 'SET @last = "$last",' . 'SET @email = "$email",' . 'SET @user_name = "$user_name",';

to

$query = 'SET @first = "$first",' . ' @last = "$last",' . ' @email = "$email",' . ' @user_name = "$user_name",';

The syntax for SET is SET variable_assignment [, variable_assignment] ... but you were using SET variable_assignment [, SET variable_assignment] ... which would cause an error.

To see if your first query has an error, try this:

$query = 'PREPARE statement FROM "INSERT INTO user_master VALUES(?,?,?,?)"';
$res = mysql_query($query);
if (!$res) die(mysql_error());

回答2:

There are 2 faults with your code.

1. You are trying to run several queries in one call to mysql_query, while you have to run each of them separately
2. inline prepared statements you're using are useless to prevent sql injections, as you are creating an SQL query the usual way, adding data directly. So, you have to format it as usual, making prepared statements quite useless.

So, if Scarlett O'Hara or Bobby Tables will decide to join your site - you're in trouble.

Therefore.

• Use inline prepared statements only to play with them in console
• If you want to use native prepared statements from PHP application - use PDO.

• Always format your queries properly, i.e.

  $last = mysql_real_escape_string($_POST['last']);
  

  if you going to insert it as a string in your query

回答3:

I think you have a simple typing error at

$query = 'EXECUTE statement USING @first,@last,@email,@user';

mysql_query;

where yo forgot to pass the query to mysql_query function so change it to

$query = 'EXECUTE statement USING @first,@last,@email,@user';
mysql_query($query);

Hope it works