escape special characters in database select query

Question

I have the following query which does not work in case that the name contain special characters:

 case 'byMovie':

  $items_per_page = 20;
  $offset = 0;
  $page_count = 0;

  include('Connection.php');
  $query1 = $conn->prepare("
SELECT DISTINCT s.starName
              , s.starImdbID
              , s.movieName
              , p.posterLink 
           FROM star_film as s 
           LEFT 
           JOIN star_Posters as p 
             ON s.starImdbID = p.starImdbID  
          WHERE s.movieName LIKE :q
");
  $query1->execute(array(':q' => '%' . $searchText . '%'));
  $row_count = $query1->rowCount();
  $page_count = (int)ceil($row_count / $items_per_page);
  $page = min($page_count, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
           'options' => array(
           'default'   => 1,
           'min_range' => 1,
          ),
      )));
       if($page > $page_count) { //double check that request page is in range
           $page = 1;     // error to user, maybe set page to 1
       }

   $offset = ($page - 1) * $items_per_page;
   $query = $conn->prepare("SELECT DISTINCT s.starName, s.starImdbID, s.movieName, p.posterLink FROM star_film as s LEFT JOIN star_Posters as p ON s.starImdbID = p.starImdbID  WHERE s.movieName LIKE :q LIMIT " . $offset . "," . $items_per_page);
   $query->execute(array(':q' => '%' . $searchText . '%'));

break;
}

for example if the movie name is 'a fish story' it shows me this error:

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 '-20,20' at line 1' in /var/www/searchcast.php:90 Stack trace: #0 /var/www/searchcast.php(90): PDO->prepare('SELECT DISTINCT...') #1 {main} thrown in /var/www/searchcast.php on line 90

I tried with adding mysql_real_escape_string(s.movieName) as suggested here, but it didn't work too.

Could someone kindly help me?

Thanks

@mihai: even without offset art when I write query directly in DB, again I receive the same error: "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 'a fish story'' " — mOna, Feb 07 '15 at 13:39
CAn you edit your question with your query and/or the values in those variables? — Mihai, Feb 07 '15 at 13:41
Right before $offset = ($page - 1) * $items_per_page; echo out you $page variable — Mihai, Feb 07 '15 at 14:23

score 0 · Answer 1 · answered Feb 07 '15 at 15:26

As you can read in the documentation:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

I think the error is related to offset value you're using (apparently -20). You have to figure out a way to avoid that number to be negative.

Seeing your code, it becomes negative when $page = 0 which translates in:

$offset = (0 - 1) * 20 = -20;

and hence the error. You could force the value of $page to be one when it's 0 or something like that.

Hope it helps.

escape special characters in database select query

1 Answers1