how to Check string present in database column?

Question

I have document table with fields id, sender_id, receiver_id and receiver_id contains string value like U12,U13,U14 now I currently logged in user and I want to find all records in which receiver_id contains my user_id means my user_id is U13. now how to write the query to fetch records.

my query is:

$selDoc="SELECT * FROM documents WHERE sender_id='U".$_SESSION['userId']."' OR  "U".$_SESSION['userId']." IN (receiver_id) ORDER BY id DESC";

but I got the error Unknown column 'U13' in 'where clause'

score 4 · Accepted Answer · edited May 23 '17 at 12:12

4

You string concatenation is messed up, it should be: (further simplified)

$selDoc="SELECT * FROM documents WHERE 'U".$_SESSION['userId']."' IN (senderID,receiver_id) ORDER BY id DESC";

when the statement above is parsed, it will then look like this:

SELECT * 
FROM   documents 
WHERE  'UXX' IN (senderID,receiver_id)  // where XX is the userID
ORDER  BY id DESC

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:12

Community

1
1

answered Apr 20 '13 at 06:05

John Woo

258,903
69
498
492

@JW the right syntax for `IN` is `WHERE column_name IN (value1,value2,...)` – Yogesh Suthar Apr 20 '13 at 06:10
once timer stopped i will accept your answer – Yadav Chetan Apr 20 '13 at 06:10
@YogeshSuthar No, `column_name IN (value1,value2,...)` is very different from `value1 IN (col1, col2, col3,...)` – John Woo Apr 20 '13 at 06:12
@JW its not working for comparing in string – Yadav Chetan Apr 23 '13 at 10:26
`find_in_set('U".$_SESSION['userId']."',receiver_id)` will work correctly – Yadav Chetan Apr 23 '13 at 11:03
means it works fine if receiver_id contains only one value but in case of string it not works for that find_in_set() works – Yadav Chetan Apr 23 '13 at 11:19

score 1 · Answer 2 · answered Apr 20 '13 at 06:07

You're missing a quote there. Furthermore you should consider using bind variables in your query (assigning the values to look for to variables and then using placeholders in your query) to avoid problems of SQL injection (If you dont know what that is, look it up, it is vitally important to know!)

how to Check string present in database column?

2 Answers2

Linked