PDO wordpress query

1 Views

I keep getting

ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You
have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near

Read More

Please help! =]
I’ve checked that this literal query in phpmyadmin works. 

SELECT * 
FROM  `wp_customgravityall` 
WHERE  (`time` >= '2015-09-01 00:00:00' AND `time` <= '2015-09-13 23:59:59')
ORDER BY  `wp_customgravityall`.`time` DESC

But my code below doesn’t work. 

<?php
$servername = "http://www.myhomepagenamethingy.com/";
$username = DB_USER;
$password = DB_PASSWORD;
$dbname = DB_NAME;

$table_name = 'wp_' . 'customgravityall';

$from_date  = isset( $_GET['from_date'] )   ? $_GET['from_date'] . " " . "00:00:00" : '';
$to_date    = isset( $_GET['to_date'] )     ? $_GET['from_date'] . " " . "23:59:59" : '';


try {
    $conn = new PDO('mysql:$servername;dbname=$dbname', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $statement = $conn->prepare(
        "SELECT * 
        FROM ? 
        WHERE `time` >= ? AND `time` <= ?
        ORDER BY `wp_customgravityall`.`time` DESC"
    );
    $statement->bindValue(1, $conn->quote($table_name), PDO::PARAM_STR);
    $statement->bindValue(2, $conn->quote($from_date), PDO::PARAM_STR);
    $statement->bindValue(3, $conn->quote($to_date), PDO::PARAM_STR);
    var_dump($statement);
    $statement->execute();
} catch(PDOException $e) {
    echo 'PDO ERROR: ' . $e->getMessage();
}
?>
Post Views: 1

Related posts

2 comments

  1. You’re trying to bind a table with FROM ?

    The rule in prepared statements is you cannot bind tables/columns.

    You either select a table name, assign a variable to a table name, or use a safelist.

    Example of setting a table name to a variable:

    $table = "table_name";

    then

    SELECT FROM $table

    or with ticks around the table name, should your table contain a character that MySQL will complain about, such as a space or hyphen:

    SELECT FROM `$table`

    In your case, the variable to use is $table_name

    • You will then need to readjust your binds accordingly.

    Another thing I spotted is that you are using single quotes here:

    $conn = new PDO('mysql:$servername;dbname=$dbname', $username, $password);

    Variables do not get parsed in single quotes (unless concatenated), use double quotes:

    $conn = new PDO("mysql:$servername;dbname=$dbname", $username, $password);

    However, your construct seems off, so you “might” have to adjust it.

    Example from the manual:

    $dbh = new PDO("mysql:host=localhost;dbname=test", $user, $pass);

    However, I may be wrong about this since I do believe there are a few methods to construct a connection with PDO.

    Therefore change it to:

    $dbh = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

    Plus, make sure those constants have been correctly assigned.

    These are considered as constants and not strings:

    $username = DB_USER;
$password = DB_PASSWORD;
$dbname = DB_NAME;

    If those are not predefined constants, you need to wrap those in quotes:

    $username = "DB_USER";
$password = "DB_PASSWORD";
$dbname = "DB_NAME";

    Then this:

    $servername = "http://www.myhomepagenamethingy.com/";

    That should more than likely be localhost set in quotes, or an IP address depending on how your system is setup, and is the usual setting and not a URL. If that doesn’t work out for you with what I already gave you in my answer, you will need to find out what your host setting is.

  2. MY SOLUTION FOUND:

    The solution found was to change the username and password in the file that connects the cms with the database and I saw that suddenly it could be the username that has a dot or is the same domain name.

    PROCESS STEPS OF to UP TO FIND CONCLUSION

    In WordPress I was having a problem between mysqli and PDO.
    I solved it with the command yum update & yum upgrade, no centos and he gave me the following updates:

    ================================================== 
   ==============================
 Package Arch Version Repository Size
 ==================================================                             
==============================     
Installing:
 lsphp73-mysqlnd x86_64 7.3.15-1.el7 litespeed 132k

         replacing lsphp73-mysqlnd.x86_64 7.3.14-1.el7
    Updating:
      lsphp73 x86_64 7.3.15-1.el7 litespeed 5.0 M
    lsphp73-bcmath x86_64 7.3.15-1.el7 litespeed 27 k
      lsphp73-common x86_64 7.3.15-1.el7 litespeed 650 k
     lsphp73-gd x86_64 7.3.15-1.el7 litespeed 114 k
     lsphp73-imap x86_64 7.3.15-1.el7 litespeed 32k
     lsphp73-mbstring x86_64 7.3.15-1.el7 litespeed 559 k
     lsphp73-opcache x86_64 7.3.15-1.el7 litespeed 191 k
     lsphp73-pdo x86_64 7.3.15-1.el7 litespeed 67 k
      lsphp73-process x86_64 7.3.15-1.el7 litespeed 29 k
      lsphp73-soap x86_64 7.3.15-1.el7 litespeed 120 k
      lsphp73-xml x86_64 7.3.15-1.el7 litespeed 126 k
      openlitespeed x86_64 1.6.9-1.el7 litespeed 37 M

    Transaction Summary
    ================================================== ==============================
    Install 1 Package
    Upgrade 12 Packages

    Total download size: 44 M
    Downloading packages:
    No Presto metadata available for litespeed

    Total 13 MB / s | 44 MB 00:03

    Besides that mine
      fastcgi_pass unix: /var/php-nginx/15822663384347.sock/socket;

    It was causing conflict.

    but it still didn’t solve the problem.

Comments are closed.