-4

I want to use a simple query for search.

This query is working fine in another page and with another table. I just copied and pasted it.

Here is query

    $stmt = $pdo->prepare("SELECT * FROM _all_users WHERE fullname LIKE '%".$q."%' OR mobile LIKE '%".$q."%' ORDER BY time_stamp DESC");
    $stmt->execute();

It must be working. But im getting this error

Fatal error: Uncaught PDOException: 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 'OR‌ mobile LIKE '%848%' ORDER BY time_stamp DESC LIMIT 15 OFFSET 0' at line 1 in /var/www/_am/ajax/getLists.php:171 Stack trace: #0 /var/www/_am/ajax/getLists.php(171): PDO->prepare('SELECT * FROM _...') #1 {main} thrown in /var/www/_am/ajax/getLists.php on line 171

Any idea?

james hofer
  • 53
  • 8
  • 1
    What is the value of `$q`? What does the query look like after the variables are interpolated? – John Conde Jun 02 '19 at 13:36
  • 2
    Please read about **[SQL injection](https://en.wikipedia.org/wiki/SQL_injection)**. Instead of building queries with string concatenation, use **[prepared statements](https://secure.php.net/manual/en/pdo.prepare.php)** with **[bound parameters](https://secure.php.net/manual/en/pdostatement.bindparam.php)**. See **[this page](https://phptherightway.com/#databases)** and **[this post](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)** for some good examples. – John Conde Jun 02 '19 at 13:36
  • Both integer and string. I give same error for both. – james hofer Jun 02 '19 at 13:37
  • I don't think `$q` contains what you think it does. Can you please post an actual query that generates that error message? – John Conde Jun 02 '19 at 13:41
  • I knwo about injection this is why Im using pdo. This question is just about syntax error not security. – james hofer Jun 02 '19 at 13:41
  • Security always matters and we take it seriously here. If you post insecure code we will *always* point it out. You aren't the only one reading this question and its comments. Letting insecure go unchallenged hurts everyone who reads this question. – John Conde Jun 02 '19 at 13:42
  • John conde you can see an example of a $q value in error. – james hofer Jun 02 '19 at 13:42
  • 1
    Please just show **the entire query**. It's that not hard to do. If you want our help you should do what we ask. We're doing *you* a favor. Please respect that and provide us with what we need to actually help you. In fact, you should show the code that builds that query, too. – John Conde Jun 02 '19 at 13:43
  • string(3) "848"...Here it is. – james hofer Jun 02 '19 at 13:46
  • 1
    Never mind. If you can't provide what I asked for I can't help you. Good luck. – John Conde Jun 02 '19 at 13:48
  • 1
    There's little point in preparing a query if you are just going to concatenate the variables into the query string. See [this question](https://stackoverflow.com/questions/8247970/using-like-wildcard-in-prepared-statement), which might also help with your problem –  Jun 02 '19 at 14:03
  • 2
    @roberthofer From reading the above comments: understand that using PDO does not itself protect you from SQL injection. Using prepared statements will only protect you from SQL injection if you use bound parameters. Using `prepare` on a query that has no bound parameters (as you have done here) provides no protection at all. –  Jun 02 '19 at 14:14

1 Answers1

-1

Try 

$stmt = $pdo->prepare("SELECT * FROM _all_users (WHERE fullname LIKE '%".$q."%' OR mobile LIKE '%".$q."%') ORDER BY time_stamp DESC");
    $stmt->execute();

or 

$stmt = $pdo->prepare("SELECT * FROM _all_users WHERE (fullname LIKE '%".$q."%' OR mobile LIKE '%".$q."%') ORDER BY time_stamp DESC");
    $stmt->execute();
user4468920
  • 11
  • 4