Geek Answers Handbook

How to remove quotes from any string when preparing queries

$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 preparation request.

Fatal error: throw a "PDOException" exception with the message 'SQLSTATE [42000]: Syntax error or access violation: 1064 You have an error in the SQL syntax; check the manual corresponding to MySQL Server Version for the correct syntax to use next to "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} is thrown in C: \ xampp \ htdocs \ portfolio \ nasiraan \ try \ indexx.php in line 89

I am sure the solution ... remove quotes from the $desc ... but how?

+4
source share
4 answers

You will have to use literal strings, which I'm afraid because placeholders cannot contain keywords, for example, for sort order (among others):

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

Building a query this way is not so bad because there are only two options.

+6
source

Parameter markers can only be used where data values ​​should be displayed, not SQL keywords, identifiers, etc.

PREPARE Syntax

You cannot use a prepared statement with it.

  • If you want to use the simple syntax of the binding value, you can use

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

Then bind the meaning of the sign with the sign. But this query will not be optimized by MySQL .

  • If you want to “learn” the wrong order, you can use the @Jack solution, but incorrect handling can lead to incorrect results. If order is important, you need to check both values:

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

You can also wrap PDO and add the special prepare_ordered($query, $order); method prepare_ordered($query, $order); or something more complicated and put a comparison there.

Or you can use foreign liberia that has no problems with it. But you should learn the API of this.

PS I see that you are using emulated prepared statement.

+2
source

I always extend PDO and add some of my own useful stuff. So first you spread 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 the parent constructor on line 9. You should add your db info instead of uppercase letters.

Please note that DBTYPE is the type of database used. This is probably just mysql.

Now I use this when sterilizing a number of lines:

 //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);
-1
source

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

$ getRecords = $ conn-> prepare ($ query); // save my query in the variable name $ query, and inside it I passed my variable .. so now I do not need to bind it.

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

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

$ getRecords-> Execute ();

-1
source

More articles:


All Articles