[Wifidog] More MySQL Problems (With Patch) in WifiDog-Auth SQL code,

[Benoit Grégoire]

> Would be replaced by:
>
> 	SELECT *
> 	FROM users
> 	    NATURAL JOIN administrators
> 	WHERE (users.user_id = '$user' OR email = '$user')
> 	  AND (pass = '$password_hash');
>
> Ok, let me comment.
>
> In my humble opinion it is always a good id to qualify column's name with
> table's name. For an alien (like me), the question quickly raised is: where

Indeed.

> does that information come from? The second question is "why the
> administrators table is in the sight"?

Because this is part of Security.php, this code finds if the user has 
administrator access.

> Since the SQL schema is gone from the cvs server, it is a good practice to

It's not gone, it moved to sql/wifidog-postgres-schema.sql

> give some extra explanations for people to will come later to do the code
> maintenance. Let's try to imagine someone, not involved in the project at
> the moment (2004), in 5 years trying to understand how to fix a problem.

Granted, unfortunately all the comments had to be removed to accomodate MySql, 
as there is apparently no SQL standard way to join comment to tables.

> In opensource/team development, the "SELECT *" could even be a bad idea. It
> assume that the table schema is frozen for ever or, worse, that future
> changes won't affect the today's code. This is an open door to break
> everything when we just change a column type for another (char(32) to
> varchar(32) for instance) and it forces the sql engine to work harder to
> get/handle/sort/display unneeded data.

Usually when I explicitly select columns, it has more to do with making the 
query more readable than with performance.  SELECT * is just so convenient 
for debugging most queries and the performance difference for queries 
designed to return a single result is irrelevent, if measurable. 
optimization, unless specifying 

Now I just don't see how doing SELECT * assumes anything more about the 
stability of the table structure that explicitely specifying columns would.

I actually have nothing against specifying the columns, but personally see it 
as premature optimization.

> The "NATURAL JOIN" is not a good idea either. Implicit ways to join two
> tables remain valid until the next release of the software. When we move a
> project from a SQL engine to another one, we have to change the schema to
> handle the differences between the two engines. So, the slights differences
> between "implicit" things (and NATURAL JOIN is the best example where the
> programmer relies to the implicit JOIN created by common column names in
> both table) are somethimes not the same thing between the two SQL engines.
> What happen when someone decides to rename a column name in a given table
> and not do the same to all other tables relying to the previous name to
> handle NATURAL JOIN? The join is broken BUT it is a nigthmare for
> maintenance programmer to locate every occurence where an implicite
> (NATURAL JOIN) are used. With an explicit join (JOIN administrators USING
> (user_id)), if someone changes a column name in 2017, the mantenance team
> will have to do a grep with "USING (user_id)" replacing it with JOIN
> adminsitrator ON (users.user_id = administrators.new_column_name).

Ok, I have to admit that NATURAL JOIN are not a terribly good idea if you 
don't have a readable schema handy, (and we no longer have one since we 
started script-generating it).  That being said the real reason I use them is 
to keep the query readable on as single line, as just about every text editor 
will handle multiline PHP strings differently and will fuck them up if you 
try to keep them cleanly indented.

-- 
Benoit Grégoire, http://benoitg.coeus.ca/

_______________________________________________
Wifidog mailing list
Wifidog at isf.waglo.com
http://isf.waglo.com/mailman/listinfo/wifidog_isf.waglo.com