[TriLUG] SQL-fu help request

[Igor Partola via TriLUG]

Matt,

Since you mention just learning some of these things, I wanted to point out
that you had in fact two queries, not one: the inner query was:

SELECT log_id, log_operator, log_maximo, log_datetime, log_location,
log_entry, log_status, log_remedy, log_dateclosed FROM operationslog limit
$startingEntry, $entriesPerPage

and the outer query was:

SELECT * from ({INNER QUERY}) order by log_status, log_datetime DESC;

You are taking advantage of unique MySQL syntax here, and not very good
one. I would avoid that, doing more explicit subqueries instead if
necessary. Another thing you might want to keep in mind is that any
subquery is logically equivalent to a JOIN and vice versa.

Lastly, as I realized this may be your actual code, not pseudocode: do not
pass parameters to the query like this. It is *horribly* insecure and
Anonymous, Lulzsec, and all the rest will be coming for you ASAP.

Instead, you want to "parametrize" the queries, or give them placeholders
where the actual values will go. I would spend some time reading up on "SQL
injection" and take a look at
http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php.
Then I would audit all existing code for this issue and make sure it's not
present. Otherwise, you are really asking for an automated bot or malware
infected Winoze box to automatically try random sort and page URL
parameters until it manages to gain control over your database, and
possibly servers.

Basically, when using PHP, never do "hello $name", even in your head; use
sprintf() for string and the MySQLi or PDO driver for the database. "hello
$world" is very dangerous and, because of PHP's popularity tools exist that
will exploit it automatically on sites big and small.

Igor

P.S.: Or ditch PHP and move to something much more pleasant like Django,
where you will never have to worry about such things again and be about 10x
more productive. Seriously, just spend two hours on the tutorial to see
what you might be missing.