How to add variable values ​​inside pdo-> query

Question

I want to update the current code that sql is constantly injecting with PDO.

I'm currently stuck in using a variable inside a PDO request.

If I have two arguments, such as

$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 a request 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 the last line of the query ORDER BY When I replace this line with "ORDER BY Date DESC LIMIT3,3"); everything works.

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

Updated: Thanks, answer, I 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 an error occurred:

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 for 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.

Answer 1

You want to use prepared statements and query parameters , as shown below:

 $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 injection, even if you use PDO. Parameters are the only good way to prevent them.