Pragmatism in the real world

Displaying the generated SQL from a ZendDbSql object

If you use ZendDbSql to generate your SQL, then it’s useful to find out what the generated SQL looks like.

Consider code like this:

public function fetchAllWithTitleSince($title, $since)
{
    $sql = new Sql($this->dbAdapter);

    $select = $sql->select();
    $select->from($this->tableName);
    $select->columns(array('id', 'title', 'url', 'date_updated'));
    $select->where->like('title', "%$title%");
    $select->where->greaterThanOrEqualTo('date_created', date('Y-m-d', strtotime($since)));

    $statement = $this->dbAdapter->createStatement();
    $select->prepareStatement($this->dbAdapter, $statement);
    return $statement->execute();
}

To find out what the generated SQL will look like, you can use the $select‘s getSqlString() method:

$select->getSqlString();

For me, this results in lots of warnings along the lines of:

Lots of notices

This is less than helpful, so to avoid the warnings, you need to supply the correct platform information to the method:

    $select->getSqlString($this->dbAdapter->getPlatform());

So we now get:

Expected SQL string

This is much better!

3 thoughts on “Displaying the generated SQL from a ZendDbSql object

  1. I'm using zend2.2 and using the statement->getSql() command to view the generated sql, but find that it is not printing out the fully generated sql.

    I do the following:
    $sql = "SELECT * FROM mytable where code = :code and name = ':name'"
    $params = array(
    ':name'=>'Name',
    ':code'=>1234,
    );
    $statement = $dbAdapter->createStatement($sql, $params);
    $statement->prepare();
    echo $statement->getSql();
    // prints out: SELECT * FROM mytable where code = :code and name = ':name'

    My reasoning is the following:
    > I like the freedom of writing a sql statement – rather than building a sql statement like you have done above with the select() where() constructs
    > I want the protection of sql injection + improved performance by using prepared statements
    > I want to have minimal abstraction layers in my php-sql development & would like a direct way to view my generated sql (not logger plugin)

    Its great that ->getSql() exists, but not great that it doesn't print out the generated code. Then I would straight-out include my parameters in the sql … but then it seems I will lose out on the performance / protection of prepared statements.

    I am fairly new to Zend & still trying to find the right way forward in my php-sql logic that I don't find too abstracted.

  2. This is now effected by http://framework.zend.com/security/advisory/ZF2013-03. If you define the platform as described above you will still get a notice because it is attempting to use the platforms quoteValue() method. There is not currently a way to set it so that the getSqlString() method will use the quoteTrustedValue() method to build the string and return it. Because of this the only way I could find to get the raw sql string was to suppress the notice as follows:

    @$select->getSqlString($this->dbAdapter->getPlatform());

Comments are closed.