[TriLUG] SQL-fu help request

[Matt Flyer via TriLUG]

Thank you again for the sage advice.  I didn't realize that this was multiple queries with an implied join.  The advanced queries and joins is an area I still need to study more.  I'm still pretty much a novice on the DB / SQL stuff.

The select statement I listed is the actual code for the select.  Except for the local variables to calculate the pages, the fields were the literals of the column names.   When using any sort of user input, I read it, extract any intended parts using things like preg match expressions and int casting and then let that result execute a case statement with actual hard code instead of user data.  All inserts with user data are done with bind parameters and prepared statements via mysqli (I've used PDO elsewhere on other projects).

Are you saying that select statements like I used are also unsafe?

I'll check out the Django.  Thank you For the suggestion.  I've never heard of it before.  PHP came easy to me from many years of C and I never really got into other common web languages.  Content managers never really struck me either, probably cause of do many years of low level and embedded processor programming,  Have used done PERL for text manipulation, but that's about it.  

Sent from my iPad

> On Feb 23, 2015, at 7:26 PM, Igor Partola via TriLUG <trilug at trilug.org> wrote:
> 
> 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.
> -- 
> This message was sent to: Matt Flyer <matt at noway2.thruhere.net>
> To unsubscribe, send a blank message to trilug-leave at trilug.org from that address.
> TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug
> Unsubscribe or edit options on the web    : http://www.trilug.org/mailman/options/trilug/matt%40noway2.thruhere.net
> Welcome to TriLUG: http://trilug.org/welcome