Secure ezSql with safeSQL class

Post date:15/02/2011 - Posted by

ezSQL is a database class written by Justin Vincent who has been writing code since 1994. The database class is written entirely in PHP and is used on over 100,000 websites including wordpress (which runs this site!). Using ezSQL you can interact with mySQL, Oracle8, MSSQL, PostgreSQL and SQLite within any PHP application. PHP already has built in functionality for these databases, however ezSQL allows you to develop much quicker with more organized code and better debugging. Selecting data with ezSQL

In this tutorial I'm going to explain how we can use ezSQL to perform databases queries then output and manipulate the data within PHP. First, lets select data from ezSQL and output it into HTML.


 
// Include ezSQL core
include_once "ezsql/shared/ez_sql_core.php";
 
//include the Mysql Class
include('ezsql/mysql/ez_sql_mysql.php');
 
$db = new ezSQL_mysql('root','MyPass','tutorials','localhost');
 
$tutorials = $db->get_results("SELECT * FROM tutorials");
 
foreach ( $tutorials as $tutorial )
{
            // Access data using object out
            echo $tutorial->title;
            echo $tutorial->tutorial;
            echo $tutorial->author;
}

The code above will output all returned rows and loop each record echoing individual column entries.


//lets select multiple records then output it to PHP
$tutorials = $db->get_results("SELECT * FROM tutorials");
 
foreach($tutorials as $tutorial) {
 
    foreach($tutorial as $key => $value ){
 
        echo $key .': '.$value.'
';
 
    }
}

This section of code will output all returned rows and loop each record echoing column name and values.


$tutorial = $db->get_row("SELECT * FROM tutorials WHERE id = '2'");
 
//print column title of the record
echo $tutorial->title;

The above code will return just one row from the database and echo a defined value.

$tutorial = $db->get_row("SELECT * FROM tutorials WHERE id = '2'");
 
//print the entire column with the column name and value in one go.
foreach($tutorial as $key => $value) {
 
     echo $key .': '.$value.'
';
 
}

The above code will return just one row from the database and echo both coloumn name and value.

$author = $db->get_var("SELECT author FROM tutorials WHERE id = '2'");
 
echo $author;

get_var will simply return one defined column or count.

$tutorials = $db->get_results("SELECT * FROM tutorials");
 
//use ezSQL's built in print_r() - to view the returned SQL object.
$db->vardump($tutorials);

vardump() is much like print_r() on mysql array output but better for two reasons; They include the pre tag for better formatting and provides you with a total number of returned objects.
More advanced Selection with ezSQL

$tutorials = $db->get_col("SELECT title,author FROM tutorials",0);
 
print_r($tutorials);
 
foreach ( $tutorials as $tutorial )
{
            echo $tutorial;
}

Get 'one column' (based on column index) and print it, note if you change the 0 in get_col's second argument it will print the next column index which is author.

//or we can include the column name
 
    foreach($tutorials as $key => $value) {
 
        echo $key.':'.$value.'
';
 
    }

This is simply another way to loop the one column index object.

$my_tables = $db->get_results("SHOW TABLES",ARRAY_N);
 
            foreach ( $tables as $table )
            {
                        $db->get_results("DESC $table[0]");
                        $db->debug();
            }

We can also put queries within the loops of a query output, this is useful to list the tables in a particular database.

$tutorial = $db->get_results("SELECT author FROM tutorials",ARRAY_A);
 
$tutorial[0] = array("author" => "MR A Jones");
 
//or numerical...
 
$tutorial = $db->get_results("SELECT author FROM tutorials",ARRAY_N);
 
$tutorial[0] = array(0 => "Mr A Jones");
 
//lets just return one value

ezSQL can output an object which you can switch to array either associative or numerical using the above code. Note the ARRAY_N and ARRAY_A in the second argument of get_results.

$db->get_results("SELECT author FROM tutorials",ARRAY_A);
 
echo $db->num_rows;

This is the number of rows that were returned for the last query. ezSQL functions compatible with SELECT

    $db->get_results : get multiple row result set from the database (or previously cached results).
    $db->get_row : get one row from the database (or previously cached results).
    $db->get_col : get one column from query (or previously cached results) based on column offset
    $db->get_var : get one variable, from one row, from the database (or previously cached results)
    $db->debug : print last sql query and returned results (if any)
    $db->vardump : print the contents and structure of any variable
    $db->select : select a new database to work with
    $db->get_col_info : get information about one or all columns such as column name or type
    $db->hide_errors : turn ezSQL error output to browser off
    $db->show_errors : turn ezSQL error output to browser on
    $db->escape : Format a string correctly to stop accidental mal formed queries under all PHP conditions
    $db = new db : Initiate new db object.

ezSQL variables compatible with select

    $db->num_queries : Keeps track of exactly how many 'real' queries were executed during the lifetime of the current script
    $db->debug_all : If set to true $db->debug_all = true; Then it will print out ALL queries and ALL results of your script.
    $db->cache_dir : Path to mySQL caching dir.
    $db->cache_queries : Boolean flag (see mysql/disk_cache_example.php)
    $db->use_disk_cache : Boolean flag (see mysql/disk_cache_example.php)
    $db->cache_timeout : Number in hours (see mysql/disk_cache_example.php)

Inserting and Updating Data with ezSQL

Below are examples of ways to insert and update data with ezSQL.

// Include ezSQL core
include_once "ezsql/shared/ez_sql_core.php";
 
//include the Mysql Class
include('ezsql/mysql/ez_sql_mysql.php');
 
$db = new ezSQL_mysql('root','MyPass','tutorials','localhost');
 
// Insert into the database
$db->query("INSERT INTO tutorials (title,tutorial,author) VALUES ('This is a tutorial title','this is an example of a tutorial body','Mr A Jones')");

Display the generated ID of the last insert

echo $db->insert_id;

You can insert data using the code above and echo out the generated ID.

// Update the database
$db->query("UPDATE tutorials SET title = 'Changing the tutorial title' WHERE id = '2')");
 
echo $db->rows_affected;

Above code is an update statement which echo's out the number of rows affected.
ezSQL INSERT / UPDATE functions

    $db->query � send a query to the database (and if any results, cache them)
    $db->debug � print last sql query and returned results (if any)
    $db->vardump � print the contents and structure of any variable
    $db->select � select a new database to work with
    $db->hide_errors � turn ezSQL error output to browser off
    $db->show_errors � turn ezSQL error output to browser on
    $db->escape � Format a string correctly to stop accidental mal formed queries under all PHP conditions
    $db = new db � Initiate new db object.

ezSQL INSERT / UPDATE variables

    $db->insert_id � ID generated from the AUTO_INCRIMENT of the previous INSERT operation (if any)
    $db->rows_affected � Number of rows affected (in the database) by the last INSERT, UPDATE or DELETE (if any)
    $db->num_queries � Keeps track of exactly how many 'real' (not cached) queries were executed during the lifetime of the current script
    $db->debug_all � If set to true (i.e. $db->debug_all = true;) Then it will print out ALL queries and ALL results of your script.
    $db->cache_dir � Path to mySQL caching dir.
    $db->cache_queries � Boolean flag (see mysql/disk_cache_example.php)
    $db->cache_inserts � Boolean flag (see mysql/disk_cache_example.php)
    $db->use_disk_cache � Boolean flag (see mysql/disk_cache_example.php)
    $db->cache_timeout � Number in hours (see mysql/disk_cache_example.php)

SQL injection protection with ezSQL and safeSQL

Next I'll show you the best ways to select, insert and update MySQL tables with ezSQL and safeSQL to guard against SQL injection attacks from unverified data.

safeSQL is written by Monte Ohrt and provides protection against SQL injection. It will work with any ANSI SQL-92 database including MySQL.

safeSQL uses printf style variables, here is a useful list for your reference.


    %i, %I � cast to integer
    %f, %F � cast to float
    %c, %C � comma separate, cast each element to integer
    %l, %L � comma separate, no quotes, no casting
    %q, %Q � quote/comma separate
    %n, %N � wrap value in single quotes unless NULL


// Include ezSQL core
include_once "ezsql/shared/ez_sql_core.php";
 
//include the Mysql Class
include('ezsql/mysql/ez_sql_mysql.php');
 
//include the safeSQL class my monte Ohrt
require 'SafeSQL.class.php';
 
//initiate a new ezSQL class
$db = new ezSQL_mysql('root','','testroom','localhost');
 
//initiate a new safeSQL class
$safesql = new SafeSQL_MySQL;
 
//run the query through SafeSQL
$query = $safesql->query("select * from tutorials where title = '%s'", array("it's an escaped query"));
 
echo $query;
 
//Get Results
$results = $db->get_results($query);
 
$db->vardump($results);

The above code will make our query safe then pass the entire SQL query over to ezSQL for processing.

//using IN with multiple ID's
 
$ids = array("3","2","erros's");
 
$safesql =& new SafeSQL_MySQL;
 
$query_string = "select * from tutorials where id in (%q)";
 
$query = $safesql->query($query_string, array($ids));
 
$results = $db->get_results($query);
 
$db->vardump($results);

The next piece of code will make safe multiple ID's passed from an array using MySQL's IN function.

$title = "My esSQL and safeSQL tutorial";
$ids = array("1","2","ecapeID's");
$author = "Lincoln's best";
 
$query_string = "select *
                from tutorials
                where title  =  '%s'
                and id in (%q)
                and author >=  %i
              [ and seo_url_title =  '%S' ]";
 
$query = $safesql->query(
            $query_string,
                array(
                    $title,
                    $ids,
                    $author,
                    $title
                )
            );
 
echo $query;

Next we have a snippet of PHP which will sanitize multiple clauses in a WHERE statement.

$foobar = array("This is' a esSQL Tutorial","This i's a tutorial","Mr A Jone's");
 
   $safe_q = $safesql->query(
               "insert into tutorials (title,tutorial,author) values (%q);",
               array($foobar)
               );
 
   $db->query($safe_q);

Again, we have an example of basic Insert which is protected against SQL injection.

$query_string = "update tutorials SET title  = '%s' author = '%s' WHERE id = %n;";
 
echo  $query = $safesql->query($query_string,array("Thi's is the new title","Mr Jones","2"));
 
$db->query($safe_q);

Finally we have an example of an injection safe UPDATE query.

Downloads and Source

ezSQL � Homepage | Download

safeSQL � Homepage | Download

Main Menu

Users Area

Comments

RAPIDQ

Comments

Insert your comment

Users

Videos

Scripts & Tutorials

50 necessaries php tools

FBJS Quick Jump Menu for a FBML Facebook Platform App

How to manage your online reputation, free tools forcommunity managers

PHP - The Singleton Pattern

PHP - calculating distance between two points

PHP - Download file with speed limit

PHP - Save remote images on our server using CURL

PHP - verify file existence in a local server

Tag Clouds