PHP/SQL PDO Prepare/Execute Insert Statement not Working

Question

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!

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
– Brad, Commented Apr 23, 2013 at 1:24
@JohnVanDeWeghe, well, there are no errors showing, but the table is not being updated. — user2288151
– user2288151, Commented Apr 23, 2013 at 1:26
@Brad, oh! Okay, so that would be the equivalent of me using my previous method. Thanks. — user2288151
– user2288151, Commented Apr 23, 2013 at 1:26
@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
– user2288151, Commented Apr 23, 2013 at 1:40

Community · Accepted Answer · 2017-05-23 11:43:18Z

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

MySQL - when to use single quotes, double quotes, and backticks?

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Apr 23, 2013 at 1:31

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2288151 Over a year ago

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.

Collectives™ on Stack Overflow

PHP/SQL PDO Prepare/Execute Insert Statement not Working

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related