how to remove quotes of any string when preparing queries

Question

$desc = 'DESC';

$getRecords = $conn->prepare('SELECT * FROM `courses` ORDER BY `id` :sort LIMIT :limitInc, :limit ');

$getRecords->bindValue(':limit',$limit,PDO::PARAM_INT);  // working

$getRecords->bindValue(':limitInc',$limitInc,PDO::PARAM_INT); // working

// *** The line below isn't working ***
$getRecords->bindValue(':sort', $desc ,PDO::PARAM_STR); // not working

$getRecords->execute();

I am trying to call $desc in my prepare query..

Fatal error: Uncaught exception 'PDOException' with message '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 ''DESC' LIMIT 0, 5' at line 1' in C:\xampp\htdocs\portfolio\nasiraan\try\indexx.php:89 Stack trace: #0 C:\xampp\htdocs\portfolio\nasiraan\try\indexx.php(89): PDOStatement->execute() #1 {main} thrown in C:\xampp\htdocs\portfolio\nasiraan\try\indexx.php on line 89

i am sure the solution is.. to remove quotes from the string $desc... but how ??

Answer 1

Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

PREPARE Syntax

You cannot use prepared statement with it.

• If you want use simple syntax of bind value you could use

  SELECT * FROM `courses` ORDER BY `id`*:sort LIMIT :limitInc, :limit 
  

Then bind signed number value. But this query will not be optimized by MySQL.

• If you want to 'swallow' wrong order you can use @Jack's solution, but mistyping in direction could get wrong results. If order is important you have to check both values:

  strcasecmp($desc, 'DESC') && strcasecmp($desc, 'ASC') ? error() : $desc;
  

Also you can wrap PDO and add special method prepare_ordered($query, $order); or something more complicated and put comparison there.

Or you could use foreign libriary which has no problem with it. But you must to learn API of it.

P.S. I see that you are using emulation of prepared statement.

Answer 2

$query = 'SELECT * FROM courses ORDER BY id '.$desc .' LIMIT :limit, :limitInc';

$getRecords = $conn->prepare($query); // store my query in a variable name $query and inside it i passed my variable.. so now i don't need to bind it..

$getRecords->bindValue(':limit',$limit,PDO::PARAM_INT);

$getRecords->bindValue(':limitInc',$limitInc,PDO::PARAM_INT);

$getRecords->execute();

Answer 3

I always extend PDO and add some of my own handy things. So first you extend like this:

<?php

    //Database class
    class db extends Pdo{
        public function __construct(){
            global $conf;
            try
                {   
                    parent::__construct('DBTYPE:dbname=DBNAME;host=DBHOST', 'DBUSER', 'DBPASS');

                        $this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
                        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                }
            catch(PDOException $e){
                throw new myPdoException($e);
            }
        }

        public function quest($queryString){
            try
                {
                    $query = $this->query($queryString);

                        return $query;                      
                }
            catch(PDOException $e){
                throw new myPdoException($e);
            }
        }

        public function doPrepare($queryString, $param){
            try
                {
                    $query = $this->prepare($queryString);

                    $query->execute($param);

                        return $query;
                }
            catch(PDOException $e)
            {
                throw new myPdoException($e);
            }
        }

        public function doPrepareBind($queryString, $param){
            try
                {
                    $query = $this->prepare($queryString);

                        foreach($param as $par){
                            switch($par[2]):
                                case 'int':
                                    $query->bindParam($par[0], $par[1], PDO::PARAM_INT);
                                break;

                                case 'str':
                                    $query->bindParam($par[0], $par[1], PDO::PARAM_STR);
                                break;
                                case 'blob':
                                    $query->bindParam($par[0], $par[1], PDO::PARAM_LOB);
                                break;

                                default:
                                    $query->bindParam($par[0], $par[1], PDO::PARAM_STR);
                                break;
                            endswitch;
                        }

                    $query->execute();

                        return $query;
                }
            catch(PDOException $e)
            {
                throw new myPdoException($e);
            }
        }
    }

    class myPdoException extends PdoException{
        private $_debug = DB_DEBUG;

        public function __construct($e){
            parent::__construct($e);

                $this->showException();
        }

        private function showException(){
            if($this->_debug){
                echo
                "<div id='transparant'><div id='error'><br /><br />" . 
                     $this->message
                . "<br /><br /><br /></div></div>";
            }
            else{
                echo    "<div id='transparant'><div id='error'><br /><br />
                            Er is iets mis gegaan, probeer later nog eens.<br />Sorry voor het ongemak.
                        <br /><br /><br /></div></div>";
            }
        }
    }

?>

You see a parent constructor on the 9th line. You have to add your db information in place of the capital letters.

Note that DBTYPE is the type of database-service you are using. Probably its just mysql.

Now this is how I use this when sterilizing a series of strings:

//first include db class I made above.
$db = new db();

$query = "INSERT INTO `database`.`users` (`id`, `naam`, `email`, `pass`, `key`, `status`) VALUES (NULL, :name, :mail, :pass, '$key', '0')";
$param = array(
                    array(':name', $_POST['name']),
                    array(':mail', $_POST['mail']),
                    array(':pass', $pass_hash)
                );
$query = $db->doPrepareBind($query, $param);

Answer 4

You would have to use literal strings I'm afraid, because placeholders can't contain keywords such as those for sorting order (amongst others):

$query = sprintf('SELECT * FROM `courses` ORDER BY `id` %s LIMIT :limitInc, :limit ', 
    strcasecmp($desc, 'DESC') === 0 ? 'DESC' : 'ASC')
);
$getRecords = $conn->prepare($query);

Building the query this way isn't so bad, because there are only two options.