Prepared Statements Debugging Snippets

format-stmt.php

<?php

function format_sugar_query(SugarQuery $query)
{
    return format_builder(
        $query->compile()
    );
}

function format_builder(\Doctrine\DBAL\Query\QueryBuilder $builder)
{
    return format_stmt(
        $builder->getSQL(),
        $builder->getParameters()
    );
}

function format_stmt($sql, array $params)
{
    $chunks = array();
    $currentQuote = false;

    for ($start = $i = 0, $length = strlen($sql); $i < $length; $i++) {
        if (!$currentQuote && ($sql[$i] == "'" || $sql[$i] == '"')) {
            $currentQuote = $sql[$i];
        } elseif ($currentQuote && $sql[$i] == $currentQuote && $sql[$i - 1] != '\\') {
            $currentQuote = false;
        } elseif (!$currentQuote && $sql[$i] == '?') {
            $chunks[] = substr($sql, $start, $i - $start);
            $start = ++$i;
        }
    }

    if ($currentQuote) {
        return 'The statement contains non-terminated string literal';
    }

    $count = count($chunks);

    if (count($params) != $count) {
        return sprintf(
            'The statement expects %d parameters, %d given',
            $count,
            count($params)
        );
    }

    $chunks[] = substr($sql, $start);

    foreach (array_values($params) as $i => $param) {
        if (is_string($param)) {
            $sql = sprintf("'%s'", addslashes($param));
        } elseif (is_bool($param)) {
            $sql = (int) $param;
        } elseif ($param === null) {
            $sql = 'NULL';
        } else {
            $sql = $param;
        }

        array_splice($chunks, $i * 2 + 1, 0, $sql);
    }

    return implode('', $chunks);
}