1

So I'm trying to use PDO objects on my site instead of the old method (especially since I heard it is better for and am in the process of updating a bunch of queries. But for some reason, I can not get prepare/execute to work no matter what I do.

dbconnect.php:

try {
    $main = new PDO("mysql:dbname=$dbmain;host=$dbhost", $dbuser, $dbpassword);
    $tracker = new PDO("mysql:dbname=$dbtracker;host=$dbhost", $dbuser, $dbpassword);
} catch (PDOException $ex) {
    echo "Connection failed: " . $ex->getMessage();
}

tracker.php

include 'dbconnect.php';

$page = $_SERVER['PHP_SELF']; //Get page name
$ip = $_SERVER['REMOTE_ADDR'];   //Get the IP address
$browser = $_SERVER['HTTP_USER_AGENT']; //Get the browser name

if(isset($_SERVER['HTTP_REFERER'])) {
    $referer = $_SERVER['HTTP_REFERER']; //Get the page the visitor came from
}
else { //If not refered from any page, referer should be blank or error occurs
    $referer = "";
}

$result = $tracker->prepare("INSERT INTO 'pages' ('page', 'ip', 'browser', 'referer') VALUES (:page, :ip, :browser, :referer)");
$result->execute(
            array(
                ':page' => $page,
                ':ip' => $ip,
                ':browser' => $browser,
                ':referer' => $referer
                )
            );

Secondly, could someone explain to me why I shouldnt use query() for everything? Right now I see that I should use query for non-dynamic queries, and prepare/execute for dynamic, but query works for both.

Thanks!

billinkc
  • 59,250
  • 9
  • 102
  • 159
user2288151
  • 43
  • 1
  • 7
  • 3
    Any errors? What's wrong? – John V. Apr 23 '13 at 01:22
  • The whole point of using prepared/parameterized queries is so that you separate the data from the command. This makes SQL injection attacks impossible. If you use `PDO::query()` with arbitrary data, you don't have this separation, and SQL injection can occur. – Brad Apr 23 '13 at 01:24
  • @JohnVanDeWeghe, well, there are no errors showing, but the table is not being updated. – user2288151 Apr 23 '13 at 01:26
  • @Brad, oh! Okay, so that would be the equivalent of me using my previous method. Thanks. – user2288151 Apr 23 '13 at 01:26
  • @Brad sorry man, I tried removing my comment when I seen that, you are right, my bad. – Chris Apr 23 '13 at 01:28
  • @user2288151, Add this to get PDO to throw exceptions: `$main->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` Do the same for `$tracker`. – Brad Apr 23 '13 at 01:28
  • @Brad, when I do that, my site doesn't even load anymore, and no message is given. – user2288151 Apr 23 '13 at 01:33
  • Somewhere there has to be a message. Look in your Apache error logs. In any case, you *need* that on, so that it will have something fail if something goes wrong. Otherwise you just have "It doesn't work but I don't know why." – Andy Lester Apr 23 '13 at 01:36
  • @user2288151, That's because you have an unhandled exception now. It's being logged. Check your log files. – Brad Apr 23 '13 at 01:38
  • 1
    @AndyLester, oh my god, I can't believe I forgot to look at my error logs. I was looking at the folder where my includes are, instead of where they were actually being executed which is the main folder. It says it was a syntax error. that makes so much more sense. – user2288151 Apr 23 '13 at 01:40

1 Answers1

2

The problem I see (may could have other problem) is you are wrapping column names with single quotes. Column names and Table names are identifiers not string literals. If ever you have used a reserved keyword on them or the column name as well as table names contains spaces around them, they should be escaped with backtick not with single quote,

INSERT INTO pages (page, ip, browser, referer) VALUES (...)
Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • This worked! I honestly didn't know the difference between a backtick and single quote until I googled it. Can't believe it was that easy. – user2288151 Apr 23 '13 at 01:37