mysqli vs msql and PDO connection

Question

i am trying to make a basic cms, following the tutorial here:Cms Tut

In Article.php, he uses the mysql_escape_string($order) with a PDO connection, which now is removed from php 7, i changed to mysqli_escape_string($order) and proceedes somehow but gives the errors with the 2 parameters. I am new to php, but as i searched, i think the problem is with the PDO connection, i cannot put the connection as second argument. Any thoughts and ideas? Thanks in Advance.

Here is the code:

public static function getList( $numRows=1000000, $order="publicationDate DESC" ) {
   $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
   $sql = "SELECT SQL_CALC_FOUND_ROWS *, UNIX_TIMESTAMP(publicationDate) AS publicationDate FROM articles
           ORDER BY " . mysqli_escape_string($order) . " LIMIT :numRows";

   $st = $conn->prepare( $sql );
   $st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
   $st->execute();
   $list = array();

   while ( $row = $st->fetch() ) {
     $article = new Article( $row );
     $list[] = $article;
   }

   // Now get the total number of articles that matched the criteria
   $sql = "SELECT FOUND_ROWS() AS totalRows";
   $totalRows = $conn->query( $sql )->fetch();
   $conn = null;
   return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );
 }

After updating the code, with creating a mysqli connection, in browser there is , this error: mysqli_connect(): (HY000/2002): php_network_getaddresses: getaddrinfo failed: No such host is known.

and in server error log also this: Call to a member function real_escape_string() on boolean

The update code is:

public static function getList( $numRows=1000000, $order="publicationDate DESC" ) {
   $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
   $link = mysqli_connect(DB_USERNAME, DB_PASSWORD, DB_DSN);

   $sql = "SELECT SQL_CALC_FOUND_ROWS *, UNIX_TIMESTAMP(publicationDate) AS publicationDate FROM articles
           ORDER BY " . $link->real_escape_string($order) . " LIMIT :numRows";

   $st = $conn->prepare( $sql );
   $st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
   $st->execute();
   $list = array();

   while ( $row = $st->fetch() ) {
     $article = new Article( $row );
     $list[] = $article;
   }

   // Now get the total number of articles that matched the criteria
   $sql = "SELECT FOUND_ROWS() AS totalRows";
   $totalRows = $conn->query( $sql )->fetch();
   $conn = null;
   return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );
 }

You say you get errors, but not what the errors are, nor confirm exactly which statement is erroring. That's pretty much the most key information regarding your problem :-/ — Adam Cameron, Sep 11 '17 at 05:49
The errors is about the sqli_real_escape_string(), and is about the 2 parameters that has to take. — Phaidonas Gialis, Sep 11 '17 at 06:49
Don't *describe* the error. Update yer question and include the exact text. Not a paraphrase of it. The exact text. — Adam Cameron, Sep 11 '17 at 08:57

score 0 · Accepted Answer · answered Sep 11 '17 at 06:56

0

You need to pick one of mysql_, mysqli_ and PDO (don't pick mysql_).

You can't mix PDO and mysqli_.

You don't need to use mysqli_escape_string to defend PDO against SQL injection; it has its own methods (which you are already using for nubRows!!).

See How can I prevent SQL injection in PHP? for guidance on handling special characters with PDO.

answered Sep 11 '17 at 06:56

Quentin

914,110
126
1,211
1,335

Can you use parameters for parts of the SQL statement itself? I suspect... no? (re your comment about using params for `numRows` being relevant to a tactic for escaping the other bit too). – Adam Cameron Sep 11 '17 at 08:59

mysqli vs msql and PDO connection

1 Answers1