Am I using PDO correctly to prevent sql injections?

Question

I've spent the last day trying to figure out how to incorporate PDO into my code to prevent sql injections. This is what I have come up with. However, whenever I submit my information from the browser, it is not updated into my table and no error messages are shown. Something is wrong but I'm not sure what. I'm postive the syntax is not the problem because I've checked that multiple times. I know my database can be accessed so I'm thinking there is a problem with the way I'm using PDO. Please help me guys.

The PSBE_LOGIN contains all the information to access my database

<?php
require_once 'PSBE_LOGIN.php';
$db_server = mysql_connect($db_hostname, $db_username, $db_password);

if (!$db_server) die("Unable to connect to MySQL:" . mysql_error());

mysql_select_db($db_database, $db_server) 
or die("Unable to select database:" . mysql_error());

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

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

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="ADD RECORD" />
</form>
_END;

?>

EDIT: CODE REWRITTEN TO INCLUDE PDO API.

<?php
require_once'connection.php';

$db = new PDO('mysql:host='.$db_host.';dbname='.$db_name,$db_username,$db_pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

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

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

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="ADD RECORD" />
</form>
_END;

function get_post($var){
  return mysql_real_escape_string($_POST[$var]);
}

?>

You are using two different types of MySQL APIs, and they do **not** mix. — Funk Forty Niner, Jul 04 '14 at 14:49
Use [**`mysqli_*` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO**](http://php.net/pdo) with [**prepared statements**](http://php.net/pdo.prepared-statements). — Funk Forty Niner, Jul 04 '14 at 14:50
@Fred-ii- I deleted the first 5 lines of code and connected using PDO API. The connection was successful however the rest of my code disappeared. I could no longer use my form because it would not display. Is there anything else I'm missing? — Amuna, Jul 04 '14 at 15:48
Add `$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after the connection is opened, see if it reveals anything. Also make sure your columns are correct, no typos, letter-case etc. *However*, am unsure about your use of `get_post()`, is that a seperate function not posted? Far as I can see, your form should be displaying. Can you edit your question with your updated code? Place it below your original code as an "edit", stating it is an **edit**. — Funk Forty Niner, Jul 04 '14 at 15:59
Related: [How to add data to specific columns in mysq using placeholders/prepared statements](http://stackoverflow.com/q/24565289/53114) and [my answer](http://stackoverflow.com/a/24567193/53114) — Gumbo, Jul 04 '14 at 16:09
I noticed a dot in `('INSERT INTO classifieds(title, author, isbn) . VALUES(:title, :author, :isbn)')` so try `('INSERT INTO classifieds(title, author, isbn) VALUES(:title, :author, :isbn)')` — Funk Forty Niner, Jul 04 '14 at 16:10
@Gumbo Is the dot in your answer valid? Am under the impression the OP got the code from yours. — Funk Forty Niner, Jul 04 '14 at 16:11
@Fred-ii- Yes, it’s PHP’s string concatenation operator. — Gumbo, Jul 04 '14 at 16:11
@Gumbo Ok, thanks. All I can see that could be affecting this, is the probable missing `get_post()` function. Hard to say if OP is actually using it or not, or has simply copied some code from somewhere (or pieced together), thinking it's a core function. — Funk Forty Niner, Jul 04 '14 at 16:17
The form is now showing. I'm using get_post() method because that is the best way to retrieve information according to the textbook I'm reading. I wrote a similar code to the example in the textbook and the function worked perfectly. — Amuna, Jul 04 '14 at 16:19
So, did adding `$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` reveal any errors? Using just `$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);` may not be enough. Can you show what's inside `get_post()` function? Am just unsure whether it's a core function or not. — Funk Forty Niner, Jul 04 '14 at 16:20
Looking at the [textbook](https://books.google.com/books?id=8-QyaeyEXHkC&printsec=frontcover) he got this from, the function `get_post` is defined as `function get_post($var) { return mysql_real_escape_string($_POST[$var]); }`. This fails if no active `mysql_*` connection is existing. However, this function is actually a bad practice as it may give the impression that SQL injection is a problem of input. — Gumbo, Jul 04 '14 at 16:26
@Fred-ii- Adding that statement did not reveal anything. When I submit my query from the browser, the form goes away and I'm left with a blank screen. At the bottom of my code I called the get_post function. I will edit my post and include it there and I'll post it here. function get_post($var){ return mysql_real_escape_string($_POST[$var]); } — Amuna, Jul 04 '14 at 16:28
Ok, just as Gumbo stated and found (good work Gumbo +1), you can't use that. I suggest you debug this by getting rid of `$title = get_post('title'); $author = get_post('author'); $isbn = get_post('isbn');` and replacing it with POST variables instead. Then, it should theoretically kick into high gear ;-) That's an `mysql_` function and again, they do **not** mix. Plus, you don't need that function even if it was using PDO syntax; you're already using placeholders. So scrap `function get_post($var){ return mysql_real_escape_string($_POST[$var]); }` — Funk Forty Niner, Jul 04 '14 at 16:30

Funk Forty Niner · Accepted Answer · 2014-07-04T16:45:49.083

Get rid of both

$title = get_post('title');
$author = get_post('author');
$isbn = get_post('isbn');

and

function get_post($var){
  return mysql_real_escape_string($_POST[$var]);
}

because the function you're using is based on an mysql_ function and those two APIs do not mix.

You don't need it, because you're already using placeholders.

while replacing it with

$title = $_POST['title'];
$author = $_POST['author'];
$isbn = $_POST['isbn'];

You also need to change

$stmt = $pdo->prepare(...

to

$stmt = $db->prepare(...

given your PDO connection $db = new PDO(...

score 0 · Answer 2 · answered Jul 04 '14 at 14:50

0

You are not using them correctly. You need to connect using the PDO API (you're connecting using the mysql_ API). Otherwise, the preparation is correct.

answered Jul 04 '14 at 14:50

Kermit

33,827
13
85
121

Am I using PDO correctly to prevent sql injections?

2 Answers2