mysqli prepared statements on tables joined by a common value

[ajoo]

Hi, 

 

I have the following query 

SELECT 
user_details.User_club_ID, 
user_details.fname, 
user_details.lname, 
user_details.email, 
user_details.club_No 
club.CLUBCODE, 
club.club_id
FROM user_details, club
WHERE club_id = $cid AND user_details.club_No = club.CLUBCODE AND user_status = 'active'";

which I converted to a prepared statement as 

SELECT 
user_details.User_club_ID, 
user_details.fname, 
user_details.lname, 
user_details.email, 
user_details.club_No 
club.CLUBCODE, 
club.club_id
FROM user_details, club
WHERE club_id = ? AND user_details.club_No = club.CLUBCODE AND user_status = ?";

Please note that user_status is a field in the table user_details. The original query (non -PDO) works correctly.

 

I want to know if this is correct and that the comparison in the WHERE clause  i.e. user_details.club_No = club.CLUBCODE is security safe. If not then how should this be modified.

 

Also if there is a better way to write this statement, kindly show that as well. 

Thanks 

 

Thanks all !

Edited December 11, 2014 by ajoo

[Ch0cu3r]

You will be better of with a JOIN

SELECT 
    ud.User_club_ID, 
    ud.fname, 
    ud.lname, 
    ud.email, 
    ud.club_No 
    c.CLUBCODE, 
    c.club_id
FROM user_details AS ud
JOIN club AS c ON ud.club_No = c.CLUBCODE 
WHERE c.club_id = ? AND ud.user_status = ?";

[ajoo]

Hi Ch0cu3r, Thanks for the reply and the suggestion. I will definitely make the switch. Would that be better from a security point of view or for any other number of reasons? I am asking so that many others like me would be clear about it.

 

Thanks very much.

[Barand]

An explicit JOIN, as Ch0cU3r used, is more efficient and also better shows the structure of the query by separating the join conditions from the selection criteria in the WHERE clause.

 

Also the purpose of prepared queries is to prevent injection from user-supplied data via variables. 'active' is a constant and therefore just include it in the statement

SELECT
ud.User_club_ID,
ud.fname,
ud.lname,
ud.email,
ud.club_No
c.CLUBCODE,
c.club_id
FROM user_details AS ud
JOIN club AS c ON ud.club_No = c.CLUBCODE
WHERE c.club_id = ? AND ud.user_status = 'active' ";

[ajoo]

Thanks Guru Barand, That answers it all ! Have already changed the query to this form. Will include the active in the query too.

 

Thanks for outlining the benefits of this structure.