How to add data to specific columns in mysq using placeholders/prepared statements

Question

I'm learning how to code from a textbook and it gives an example how to add data into a table using placeholders however it does not show how to add data to only specific columns. This is what I came up with

if (isset($_POST['title']) &&
isset($_POST['author']) &&
isset($_POST['isbn']))
//This checks to see if there is a value inputted into the form at the bottom
{
$title = get_post('title');
$author = get_post('author');
$isbn = get_post('isbn');
//This retrieves information from the user and assigns it to a variable

$q = 'PREPARE statement FROM "INSERT INTO classifieds(title, author, isbn)'
        . 'VALUES(?,?,?)"';
mysql_query($q);

$q = 'SET @title = "$title",' .
     '@author = "$author",' .
     '@isbn = "$isbn",';
mysql_query($q);

$q = 'EXECUTE statement USING @title,@author,@isbn';
mysql_query($q);

$q = 'DEALLOCATE PREPARE statement';
mysql_query($q);
}

echo <<<_END
<form action="PSBE_POST_AD.php" method="post">
Title <input type="text" name="title" />
Author <input type="text" name="author" />
ISBN <input type="text" name="isbn" />
<input type="submit" value"Post Classified" />
</form>
_END;
?>

However, whenever I submit my information into the browser, I check to see if it was added via phpmyadmin and it is not. I'm making a classified website and I'll need the highest security and that's why I chose to use placeholder. I've tried looking online for a solution using many different syntax's but none of them work. So what is wrong with this code? Any advice will be appreciated.

Is there a specific reason why you're not using [mysqli prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) (or PDO equivalent)? — kero, Jul 04 '14 at 00:58
@xd6_ it's gotta be pretty damn old. those are some old school prepared statements right there. — r3wt, Jul 04 '14 at 01:27
The book was published in 2012 but it did not mention mysqli prepared statements. I will check that out. — Amuna, Jul 04 '14 at 01:31

score 0 · Answer 1 · edited May 23 '17 at 12:23

Although the mentioned textbook is right about that prepared statements are “virtually bulletproof”, when preparing the statement and setting the parameters manually, the problem with passing the parameter values properly still remains.

The example in the textbook does only use static values, which is not very helpful. However, you came up with a solution, that, besides the mentioned syntax error, would work but incorporates the values in an insecure way, which makes you still vulnerable to SQL injection.

The solution here would be using mysql_real_escape_string for the string literal values:

$q = 'SET @title = "'.mysql_real_escape_string($title).'",' .
     '@author = "'.mysql_real_escape_string($author).'",' .
     '@isbn = "'.mysql_real_escape_string($isbn).'";';

However, there are MySQL APIs that support native prepared statements where you don’t have to fiddle with mysql_real_escape_string.

Have a look the examples at How can I prevent SQL-injection in PHP?. For example, with PDO the statement preparation and execution would be just this:

$stmt = $pdo->prepare('INSERT INTO classifieds(title, author, isbn)'
                         . 'VALUES(:title, :author, :isbn)');
$stmt->execute(array('title' => $title, 'author' => $author, 'isbn' => $isbn));

This also uses a prepared statement but PDO takes care of the proper parameter passing.

How to add data to specific columns in mysq using placeholders/prepared statements

1 Answers1

Linked