uchiko/sql-maker

SQL Builder

Installs: 9

Dependents: 0

Stars: 2

Watches: 1

Forks: 1

Language: PHP

0.02 2014-07-05 22:56 UTC

README

sql-maker - SQL builder written in PHP.

SQL::Maker - SQL builder written in perl. https://github.com/tokuhirom/SQL-Maker

REQUIREMENTS

  • PHP 5.3 or later

The JSON SQL Injection Vulnerability

SQL_Maker has a JSON SQL Injection Vulnerability if not used in strict mode.

Therefore, I strongly recommend to use SQL_Maker in strict mode. You can turn on the strict mode by passing 'strict' => 1 as:

new SQL_Maker(array(..., 'strict' => 1))
new SQL_Maker_Select(array(..., 'strict' => 1))

In strict mode, array or hash conditions are not accepted anymore. A sample usage snippet is shown in below:

$builder = new uchiko\SQL\Maker(array('driver' => 'mysql', 'strict' => 1));

$builder->select('user', array('*'), array('name' => $json['name']));
// => SELECT * FROM `user` WHERE `name` = ?

$builder->select('user', array('*'), array('name' => array('foo', 'bar')));
// => Exception! Will not generate SELECT * FROM `name` IN (?, ?) any more

$builder->select('user', array('*'), array('name' => sql_in(array('foo', 'bar'))));
// => SELECT * FROM `user` WHERE `name` IN (?, ?)

$builder->select('fruit', array('*'), array('price' => sql_le($json['max_price'])));
// => SELECT * FROM `fruit` WHERE `price` <= ?

See following articles for more details (perl version)

SYNOPSIS

create instance

  $builder = new uchiko\SQL\Maker(array('driver' => 'SQLite'));

select()

  $table = 'pokedex';

  $fields = array('id', 'name');

  $where = array();
  $where['area'] = 'houen';
  $where['type'] = array( 'like' => '%electric%' );

  $opt = array();
  $opt['order_by'] = array('id');


  list($sql, $binds) =
       $builder->select($table, $fields, $where, $opt);

 /*
  * $sql   => "SELECT id, name
  *            FROM pokedex
  *            WHERE (area = ?) AND (type LIKE ?)
  *            ORDER BY id"
  * $binds => ("houen", "%electric%")
  */

WHERE

 $table  = 'pokedex';

 $fields = array('*');

 $where  = array(
               'name'      => 'Pikachu',
               'legendry'  => null,
               'area'      => array('ish', 'joto'),
               'base_hp'   => array('<=' => 40),
               'type'      => array('like' => '%electric%'),
               'id'        => uchiko\SQL\Maker::scalar( array('= (? - 0)', 25) ),
               'available' => uchiko\SQL\Maker::scalar( array('LIKE ? ESCAPE ?', '%mega\\_kick%', '\\') )
               );

 list($sql, $binds) = $builder->select($table, $fields, $where);

 /*
  * WHERE ( name = "Pikachu" ) AND
  *       ( legendry IS NULL ) AND
  *       ( area IN ("ish", "joto") ) AND
  *       ( base_hp <= 40 ) AND
  *       ( type LIKE "%electric%" ) AND
  *       ( id = (25 - 0) ) AND
  *       ( available LIKE "%mega\_kick%" ESCAPE "\" );
  */

delete()

 list($sql, $binds) = $builder->delete('pokedex', array( 'id' => '25' ));


 /*
  * $sql    => "DELETE FROM pokedex WHERE (id = ?)"
  * $binds  => (25)
  */

insert()

  $table = 'pokedex';

  $values = array(
         'id' => 25,
         'name' => 'Pikachu'
);

list($sql, $binds) = $builder->insert($table, $values);


/*
 * $sql   => "INSERT INTO pokedex (name, id) VALUES (?, ?)"
 * $binds => ("Pikachu", 25)
 */

update()

  $table = 'pokedex';

  $set   = array('type' => 'electric');

  $where = array('id' => 25);

  list($sql, $binds) = $builder->update($table, $set, $where);

  /*
   * $sql   => "UPDATE pokedex SET type = ? WHERE (id = ?)"
   * $binds => ("electric", 25)
   */

method chain

   $sql = new uchiko\SQL\Maker\Select();

   $sql
    ->addSelect('foo')
    ->addSelect('bar')
    ->addSelect('baz')
    ->addFrom('table_name')
    ->asSql();
    
    // => "SELECT foo, bar, baz FROM table