PHP 5.3 "NOWDOCS" make SQL escaping easier (PHP and Oracle: Christopher Jones)

PHP 5.3 "NOWDOCS" make SQL escaping easier

By christopher.jones on February 13, 2008 5:35 PM


<?php

// Escaping quotes or other meta characters in SQL can be painful
$sql[] = 'select sys_context(\'userenv\', \'service_name\') from dual';

// Unless you get lucky with your quoting style
$sql[] = "select sys_context('userenv', 'service_name') from dual";

// Escaping strings is even more annoying in XQUERY syntax which, when
// embedded in SQL, uses both single and double quotes and its own
// dollar-prefixed variables
$sql[] = 'select xmlquery(\'for $i in ora:view("hr","locations")/ROW
          return $i/CITY\' returning content) from dual';
$sql[] = "select xmlquery('for \$i in ora:view(\"hr\",\"locations\")/ROW
          return \$i/CITY' returning content) from dual";

// Even with PHP's "Heredoc" syntax something will need escaping.
// Note the final END can't have leading whitespace, nor trailing
// whitespace after the semi-colon
$sql[] = <<<END
select xmlquery('for \$i in ora:view("hr","locations")/ROW
return \$i/CITY' returning content) from dual
END;

// But with PHP 5.3's new "Nowdoc" syntax no escaping is needed.
// To distinguish a Nowdoc from a Heredoc, the initial keyword END is
// enclosed in single quotes.
$sql[] = <<<'END'
select xmlquery('for $i in ora:view("hr","locations")/ROW
return $i/CITY' returning content) from dual
END;

// Thanks Gwynne and Dmitry for the new feature!

$c = oci_pconnect('hr', 'hrpwd', 'localhost/orcl');

foreach ($sql as $stmt) {
    do_query($c, $stmt);
}

function do_query($c, $stmt)
{
    $s = oci_parse($c, $stmt);
    oci_execute($s);
    while ($row = oci_fetch_array($s, OCI_ASSOC)) {
        foreach ($row as $item) {
            echo "$item ";
        }
        echo "\n";
    }
}

?>

Tags:

Permalink

Comments (6)

James Logsdon:

Wow, this will be really handy.

Posted by James Logsdon | February 13, 2008 7:15 PM

Posted on February 13, 2008 19:15

Schmalls:

I didn't realize how recently this was committed. I had a snapshot from Monday, and went to try it out. Then I searched the CVS commit list and found out it was committed Tuesday. I do like this extra functionality. One benefit of the syntax that I haven't seen anyone talk about is for runtime created functions. It will definitely make them look a lot cleaner.

Posted by Schmalls | February 13, 2008 8:32 PM

Posted on February 13, 2008 20:32

Unomi:

I noticed the NOWDOCS start with $sql[] = <<<'END'

Are these singlequotes needed or is it a typo?

- Unomi -

Posted by Unomi | February 14, 2008 2:36 AM

Posted on February 14, 2008 02:36

Christopher Jones:

@Unomi: the single quotes are needed for Nowdocs. Without them, the block is a Heredoc and variable interpolation occurs.

Posted by Christopher Jones | February 14, 2008 12:09 PM

Posted on February 14, 2008 12:09