Clear way to check friends in mysql query

Question

First of all I have an id that corresponds to the users table identifier and I pass by get to php.
I want to check: nickname.users, friends.id_friends, friends.id_adder, friends.id_added and friends.id_tipus but in a best way checking friends.id_adder, friends.id_added and getting the nickname correctly. The code I make works.

My query:

    SELECT users.nickname,
            friends.id_friends, friends.id_adder, friends.id_added, friends.id_tipus 
            FROM friends 
            LEFT JOIN users ON users.id=friends.id_added OR users.id=friends.id_adder
            WHERE 
            (friends.id_adder='".$id."' AND (SELECT nickname FROM users WHERE id='".$id."')<>users.nickname) 
            OR 
            (friends.id_added='".$id."' AND (SELECT nickname FROM users WHERE id='".$id."')<>users.nickname);

@Barmar Yes but I'm learning mysql, I don't know if there's a better option to do it — Roger RV, Mar 01 '17 at 21:54
The first thing that jumps off the page: don't include `$id` like this: it makes your code vulnerable to SQL injection. Use prepared statements instead. — trincot, Mar 01 '17 at 21:56

score 2 · Accepted Answer · edited Sep 18 '17 at 07:50

2

I would transform that query into this:

SELECT    u1.nickname,
          friends.id_friends, 
          friends.id_adder, 
          friends.id_added, 
          friends.id_tipus 
FROM      users u1
LEFT JOIN friends  ON u1.id IN (friends.id_added, friends.id_adder)
LEFT JOIN users u2 ON u2.id IN (friends.id_added, friends.id_adder)
                   AND u2.nickname <> u1.nickname
WHERE     u1.id = ?

You should not inject the $id value into your SQL string, as this makes your code vulnerable to SQL injection. Instead use prepared statements.

edited Sep 18 '17 at 07:50

CL.

173,858
17
217
259

answered Mar 01 '17 at 22:09

trincot

317,000
35
244
286

Can I use http://php.net/manual/es/function.mysql-real-escape-string.php to block sql injection? – Roger RV Mar 01 '17 at 22:13
1

You should walk away from `mysql_` functions: they have been deprecated for years now, they are not even supported since PHP 7. Use PDO or `mysqli`. As to that function, it is [not bullet proof for just any SQL](http://stackoverflow.com/a/5741264/5459839). – trincot Mar 01 '17 at 22:17
I could implement mysqli prevention works well, thanks a lot – Roger RV Mar 01 '17 at 23:06

Clear way to check friends in mysql query

1 Answers1