Adam Jimenez | PHP/MySQL INSERT

PHP/MySQL INSERT

PHP is a very flexible language. Maybe too flexible. There are a lot of ways to skin a cat. Some ways are a lot better than others, and some have security vulnerabilites.

After a day of monitoring web development forums it's quickly become apparent that beginners get very confused about MySQL.

In this post I will focus on inserting data into a database. There are a few different ways to insert data into a database. Imagine we are capturing some user data. This is how I would do it:

<?php
mysql_query("INSERT INTO `table` SET
    date=NOW(),
    name='".escape($_POST['name'])."',
    surname='".escape($_POST['surname'])."',
    email='".escape($_POST['email'])."',
") or trigger_error("SQL", E_USER_ERROR);
?>

Now there are a few things going on here:

the query is broken up onto multiple lines and indented for readability.
sql syntax is in uppercase, again for readability
using NOW() instead of timestamps
using a custom function called escape which is just a wrapper for mysql_real_escape_string; to save typing
Using concatenation for readability.
custom error handler - which in my case sends me an email to notify me of problems.

Security

The escape function is VERY important as it will prevent basic sql injection attacks. A lot of beginners forget this.

You should avoid printing error messages to the screen, as this can aid hackers. So try not to use "or die(mysql_error())". Instead use a custom error handler as demonstrated above.

18/05/2009 permalink | Posted in web development | 7 Comments »

PDO has the advantage that you can change which database type you use (e.g. MySQL to PostgreSQL or SQLite) just by changing the constructor. In the real world, it's up to you to decide on an individual basis how useful that is - for me, I'm unlikely to use anything other than MySQL.

In my view the MySQL(i) extension in PHP is unwieldy and confusing (especially for newbies). I'm using Zend Framework on all my new projects and the ease of use of Zend_Db is incredible. e.g.

// inserts a record (automatically escapes etc.) and returns auto_increment ID
$data = array( 'name' => 'Blue Widget', 'size' => 'Large' );
$id = $db->insert( 'widgets', $data );

// returns an array of all widgets
$widgets = $db->fetchAll( "SELECT * FROM widgets" );

// returns an assoc. array of id => name
$widgets = $db->fetchPairs( "SELECT id, name FROM widgets" );

Having just converted a legacy project to Zend_Db I've halved the amount of DB related code and increased the security.
Steve Hollis on 14/07/2009

Error in previous code - to get auto_increment ID you need to call $db->lastInsertId();
Steve Hollis on 14/07/2009

Thanks for your input Steve.

When using zend and inserting can you still use mysql functions like NOW() ?
Also when selecting if you have a variable in your select statement, presumably you would need to manually escape it.

I watched the introductory screencasts to zend framework. It did strike me as being a bit arduous how you create new pages and having to remember the exact syntax. My homebrew system is much simpler. Altho I'm sure zend is very sophisticated and has some great features.
Adam Jimenez on 14/07/2009

To use expressions with Zend_Db you have to use Zend_Db_Expr, e.g.

$data = array(
'created' => new Zend_Db_Expr( 'NOW()' )
);

If you were writing your own SELECT statements and had a variable, then yes, you'd need to escape them manually. This is where Zend_Db_Select comes into its own - it allows you to construct SQL statements programatically.

$select = $db->select()
->from( 'widgets' )
->where( 'colour = ?', $colour );

The $colour variable is them automatically inserted where the ? appears.

The MVC components of Zend Framework are a steep learning curve - I had two or three bashes at it before I eventually persisted and reaped the rewards. It's also probably overkill for some smaller projects.

The main advantage I can see over developing your own system from scratch is the integration with ZF components like Zend_Auth, Zend_Acl, Zend_Form etc. Everything can be extended to meet your needs and I'm finding that projects come together much more quickly when you have these set of reusable components.
Steve Hollis on 15/07/2009