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

[Yanik Crépeau]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Rikhardur.EGILSSON at oecd.org wrote:
| I think this is definately a problem with MySQL, it needs the following
| change to accept the query.
|
| The code should also run normally on PostgreSQL.
|
|
| --- classes/Security.php.old	2004-09-30 16:49:17.225470526 +0200
| +++ classes/Security.php	2004-09-30 16:49:59.811366029 +0200

<offtopic>
By the way, who else had the idea to print the time
up to the nanosecond (10 power minus 9 second)? No
computer has a clock in sync with the official time
of the Bureau international de l'heure to this
precision. Even with a GPS connected with your
computer and the computer beeing just a time
server (no other task) the best you can reach
would be, at best, 100 ns precision. In fact,
according Don Mills (father of ntpd and author
of most RFCs regarding time synchronization, having
a local network in sync within few milli-seconds
would be a good goal.

Some could argue that would be a good way to
distinguish to inputs arriving at almost the
same time. That would be a bad idea to rely
to such a methodology. With computers running
between 1 and 10 GHz, there are a good probability
that two request arrives at the "same" time.
</offtopic>

| @@ -50,7 +50,7 @@
|      //$this->session->dump();
|      $user = $this->session->get(SESS_USERNAME_VAR);
|      $password_hash = $this->session->get(SESS_PASSWORD_HASH_VAR);
| -    $db->ExecSqlUniqueRes("SELECT * FROM users NATURAL JOIN administrators
| WHERE (user_id='$user' OR email='$user') AND pass='$password_hash'",
| $user_info, false);

Let me try to understand:

	SELECT *
	FROM users
	    NATURAL JOIN administrators
	WHERE (user_id = '$user' OR email = '$user')
	  AND (pass = '$password_hash');

| +    $db->ExecSqlUniqueRes("SELECT * FROM users NATURAL JOIN administrators
| WHERE (users.user_id='$user' OR email='$user') AND pass='$password_hash'",
| $user_info, false);

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 does
that information come from? The second question is "why the administrators table
is in the sight"?

If a table's name is too long, SQL provides an abreviation mecanism.

Since the SQL schema is gone from the cvs server, it is a good practice to 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.

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.

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





|      if(empty($user_info))
|        {
|  	echo '<p class=error>'._("You do not have administrator
| privileges")."</p>\n";
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBXG570OgIseWq58IRAi2NAJ93c0VEPH0Nef5HuEz4SdsWNbiFjACeOKqM
3DzqJRQ6/hoeAt4/6ZTzT5U=
=Kn4l
-----END PGP SIGNATURE-----

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