using placeholders with php and mysql to prevent injection

Question

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

Welcome to Stack Overflow! Please, don't use `mysql_*` functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://goo.gl/KJveJ). See the [**red box**](http://goo.gl/GPmFd)? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide, [this article](http://goo.gl/3gqF9) will help to choose. If you care to learn, [here is good PDO tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Jun 26 '12 at 20:06
Does this answer your question? [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Dharman, Feb 14 '20 at 17:54

score 1 · Answer 1 · answered Jun 26 '12 at 20:48

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

Your Common Sense · Answer 2 · 2013-03-12T07:11:12.570

There are 2 faults with your code.

You are trying to run several queries in one call to mysql_query, while you have to run each of them separately
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

score 0 · Answer 3 · answered Aug 02 '15 at 10:26

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

using placeholders with php and mysql to prevent injection

3 Answers3