How to add variable values inside pdo->query

Question

I want to upgrade my current code which is constantly sql injected with PDO.

Currently I'm stuck with using a variable inside a PDO query.

If I have two arguments like this

  $rowsPerPage = 3;

  // by default we show first page
  $pageNum = 1; 

  if (isset($_GET['page'])) {
     $pageNum = mysql_real_escape_string($_GET['page']);
  }

  $offset = ($pageNum - 1) * $rowsPerPage;

And I have query like this

$STH = $DBH->query("SELECT News.ID, LEFT(NewsText,650), Title, AID, Date, imgID," .
        "DATE_FORMAT(Date, '%d.%m.%Y.') as formated_date " .
        "FROM News, Categories, NewsCheck  WHERE Name LIKE '%News - Block%' AND CID=Categories.ID AND JID=News.ID ". 
        "ORDER BY `Date` DESC LIMIT $offset, $rowsPerPage");

PDO reports an error in last line of the query ORDER BY When I replace these line with "ORDER BY Date DESC LIMIT3,3"); everything work.

So how to add variable values inside PDO::query ?

Updated: Thanks to answer bellow I have updated my code like this

$STH = $DBH->prepare("SELECT News.ID, LEFT(NewsText,650), Title, AID, Date, imgID," .
            "DATE_FORMAT(Date, '%d.%m.%Y.') as formated_date " .
            "FROM News, Categories, NewsCheck  WHERE Name LIKE '%News - Block%' AND CID=Categories.ID AND JID=News.ID ". 
            "ORDER BY `Date` DESC LIMIT :offset, :rowsPerPage;");

$STH->bindParam(':offset', $offset, PDO::PARAM_STR);
$STH->bindParam(':rowsPerPage', $rowsPerPage, PDO::PARAM_STR);

$STH->execute();

But error occured:

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 ''-3', '3'' at line 1' in /pdo/test.php:42 Stack trace: #0 /pdo/test.php(42): PDOStatement->execute() #1 {main} thrown in /pdo/test..

Second Update Changed from PARAM_STR TO PARAM_INT like this

$STH->bindParam(':offset', $offset, PDO::PARAM_INT);
$STH->bindParam(':rowsPerPage', $rowsPerPage, PDO::PARAM_INT);

Everything works.

assign the query string to a variable, var_dump() the results and post here please. — Mr Griever

Pierre-Olivier Pierre-Olivier · Accepted Answer · 2012-03-30T19:45:17

You want to use prepared statements and query parameters like the following:

$sth = $dbh->prepare('SELECT your_column FROM your_table WHERE column < :parameter');
$sth->bindParam(':parameter', $your_variable, PDO::PARAM_STR);
$sth->execute();

Using variables directly in your query will not protect you from SQL injections, even if you are using PDO. Parameters are the only good way to prevent them.

How to add variable values inside pdo->query

1 Answers