Jump to content

4.0 - Prepared Statements

In 4.0, we have made changes to the database class to make use of prepared statements.

For insert and update queries, the syntax is the same as it always has been:

IPSDb::i()->insert( 'table', array( 'foo' => 'bar' ) );

However, where previously the database class would try to work out the type of variable passed to it - it now binds these to a prepared statement.

The real usefulness of this change though, is apparent when you need to use a where clause. Where previously you'd have to do something like this:

$this->DB->buildAndFetch( array( 'select' => '*', 'from' => 'table', 'where' => "foo='" . $this->DB->addSlashes( $foo ) . "'" ) );

You can now do:

IPSDb::i()->buildAndFetch( array( 'select' => '*', 'from' => 'table', 'where' => array( 'foo=?', $foo ) ) );

We calculate the datatype based on the variable datatype, so previously where you had to do things like $this->DB->setDataType( 'foo', 'string' ) when you wanted to store a value like '01' - ew) - you can now just cast the variable to whatever datatype you like.
For example, if you wanted to ensure that the variable was cast as a string to avoid issues where a user name of '007' was detected as an integer and converted to '7' then you'd use:

IPSDb::i()->buildAndFetch( array( 'select' => '*', 'from' => 'table', 'where' => array( 'foo=?', (string) $foo ) ) );

Not only is this easier to type and to read, it ensures that the database class always takes care of escaping things properly.

  • Create New...