how to Check string present in database column? [closed]

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'

Lactiferous

Answer 1

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?

Carl

@JW the right syntax for IN is WHERE column_name IN (value1,value2,...) – Aquarist

once timer stopped i will accept your answer – Lactiferous

@YogeshSuthar No, column_name IN (value1,value2,...) is very different from value1 IN (col1, col2, col3,...) – Carl

@JW its not working for comparing in string – Lactiferous

find_in_set('U".$_SESSION['userId']."',receiver_id) will work correctly – Lactiferous

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 – Lactiferous

Answer 2

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!)

Karnak