Use MySQL Gui tools to securely connect to remote database

October 27th, 2007 by Aaron

This particular example is going to be based on a connection from Windows XP using Putty, MySQL GUI tools and Dreamhost.

Quick summary of issue: I want to use MySQL Query Browser to access my database on my dreamhost account. The database allows connections from the webserver only - nothing external. I have an SSH account on the webserver.

Quick answer: This requires us to tunnel from our machine to the webserver and connect through this tunnel to the database server.

Lets take a look on how we can accomplish this:

Continue reading Use MySQL Gui tools to securely connect to remote database

Write Security Triggers Against SQL Injection

August 19th, 2007 by Aaron

An interesting idea that a colleague told me about was a ’security trigger’ in any application that has a SQL type storage engine. The trick is to make sure that your admin account is not ID #1 and that your administrative username isn’t one of the most common ones:

• admin
• root
• administrator
• webmaster
• company name / your name

Then, the next thing to do is to program a trigger in your mysql database to check against a select against ID #1 or against one of those names. This would only happen if there was some sort of sql injection being exploited on your site… (of course make sure that the ’search’ feature can’t search for those usernames either - otherwise you’ll get false positives!)

A very intriguing idea.

ODBC for UDB and PHP - How I increased performance by 400%

August 2nd, 2007 by Aaron

In our current setup at (”the triangle”), we have to use odbc connections to access our db2-udb database - and I don’t like it. But we have to stick with it - and thats the way life is. The main reason I don’t like it is the immense overhead and time it takes to execute queries. Well, I did some research and found out some interesting things. The most important of which was a cursor setting that allowed me to gain up to 400% performance. Find out how:

Continue reading ODBC for UDB and PHP - How I increased performance by 400%

PHP developer’s shortcut for optimizing mysql

July 21st, 2007 by Aaron

PHP developers, raise your hand if you run an explain on each MySQL statement you write and use in your apps! Anyone? Ok… 1… 2… thats it? Yah, I tend to forget that too, but luckily PHP allows us to cheat. Thanks PHP!

The ini directive mysql.trace_mode will generate errors on unoptimized mysql queries (ie, table scans, etc) . Combined with my new error monitoring eclipse tool, this has been saving me tons of time. It won’t solve your issue or run an explain, but it will tell you if mysql is reporting an issue with your query. Of course, you’d NEVER turn this on for production. heh.

Real world stories of a prepared statement

July 6th, 2007 by Aaron

A couple months ago, I was out in Rochester MN at IBM for a multi-day meeting about communication between the iSeries(system-i, i5, as400, whatever its called now a days) and PHP/Apache. One of the things we talked about was our use of ODBC at (”the triangle”) currently to which they asked a good question - Are we using prepared statements over odbc? Well, right now, we’re not, but I think we should. As always, its up to me to show why we should be doing this. Lets explore:

Continue reading Real world stories of a prepared statement

Random user generation - optimized

June 27th, 2007 by Aaron

I came across this blog posting about optimizing order by rand() and decided to make my queries better. Here is my real life example on how to optimize this query:

Continue reading Random user generation - optimized