Fork me on GitHub

article

mySQL Class Updated

September 12, 2005 | PHP Scripting

The change is minor but has significant meaning to the script. I added two functions – escapeData() and buildQuery().

The significance of adding these is mainly in the escapeData() function. Instead of encouraging the user the use addslashes before using submitted data or, worse yet, encouraging the user to rely on magic_quotes, the escapeData function uses the mysql_real_escape_string function to escape the data per the mySQL server’s instruction. In this case the escaping is more in tune to what the SQL server needs and is also consistent with the character encoding that the database uses. Character encoding is picky when storing binary data in the database, so this helps maintain a proper matchup of data encodings.

And example of usage would be:

$query = sprintf("SELECT * FROM table WHERE email=%1 AND name=%2",
                        $this->escapeData($email),
                        $this->escapeData($name));

The other is buildQuery. buildQuery ties in directly to escapeData in that it uses the escapeData function to put together an escaped query string like so:

$base_query = "SELECT * FROM table WHERE email=%1 AND name=%2";
$query = $db->buildQuery($base_query,$var1,$var2,etc...);

The function is nothing extraordinary (it actually emulates using sprintf with a foreach loop) but it can save you some typing on long queries that use a lot of outside data.

So, nothing huge, just trying to be correct. Download the updated dbConnect script here or peruse the source code here.

7 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  • mysql_real_escape_string is a tasty morsel!

    John Pennypacker, September 13, 2005 6:41 am | permalink

  • oh, and if you’re not doing it, here’s a neat little check to ensure that the slashes haven’t already been added and to remove them if they have been:

    if(ini_get(‘magic_quotes_gpc’)) {

    $var = stripslashes($var);

    }

    John Pennypacker, September 13, 2005 6:48 am | permalink

  • I’m using a slight modification of that:

    // Stripslashes if magic quotes has already added them
    if (get_magic_quotes_gpc()) 
        {
             $d = stripslashes($d);
        }

    Shawn, September 13, 2005 8:11 am | permalink

  • Now Shawn. What exactly is this and how do I effectively use it? Explain it to me like a 2 year old, I’m confused.

    Tom, September 13, 2005 7:58 pm | permalink

  • Here’s as productive a comment you will out of me on this one:

    But, Tom! You are two! ;o

    Joey B., September 13, 2005 8:11 pm | permalink

  • Tom, I could just tell you to RTFF – the instructions are at the bottom of the source file. And I could just point you to the first article I wrote about it. ; )

    But, I’ll see if I can explain it a bit differently. I’m probably dumbing down a bit too much here so bear with me…

    Abstraction of any kind is a way to take a function, wrap it up in a user defined class & function, and essentially redefine it. The benefit of this is that when you then use it around your site you can easily make updates to the one class and how it works to update the whole site. This is particularly beneficial when upgrading systems. For example, to move from mysql php functions in php4 to mysqli functions in php5 will require minor tweaks to this one file and I’ll be off to the races on php5 and mysql-improved functions accross my site and in any other classes that use it to manage db connections in no time at all. Simple as that. Even if I need to retool it to work with postgres or mssql, a few tweaks to the existing functions and the site can talk to a different type of database (with little tweaks to the existing query strings of course).

    This particular class is for connecting to mysql databases. It is a class that takes most of the work out of pulling data from databases and makes it an easy switch to then write back into them (ie: use a simple read only user to do most of the calls and then use a seldom called read/write user when writing to the database is required). It manages everything from selecting the database, formatting returns into numbered or associative arrays, and even error reporting and emailing of errors to the site admin. All you have to do is make sure your server address, database name, usernames and passwords are good to go. While not perfect is does do a lot. Most settings can be overridden at runtime, or locked out by the admin so that anyone else using it on the server can’t alter its usage. I’ll make better provisions for mulitple databases in the future – I just got a good idea for it while writing this…

    Does that answer the question?

    Shawn, September 13, 2005 8:38 pm | permalink

  • Wow, it sounds rather very nice. I might have to see how I can work this one out with the CMS I’m building. Hmm..

    Tom, September 13, 2005 11:50 pm | permalink

Comments are closed