PHP PDO Oracle not working for multiple statements

Question

I am using PDO in the following code to run multiple statements in one excetute and fetchall. This works for Microsoft SQL Server but is not working for Oracle.

<?php 
    $conn = new PDO("odbc:oratest", "SYSTEM", "password");
    $result = $conn->prepare("select * from all_objects; select * from all_users;");
    $result->execute();

    echo "<html><body>";
    do
    {
        echo "<table border=\"0\">";
        $row = $result->fetchAll(PDO::FETCH_ASSOC);
        if($row && count($row) > 0) {
            $keys = array_keys($row[0]);
            echo "<tr>";
            for($j = 0; $j < count($keys); $j++) {
                echo "<td>" . $keys[$j] . "</td>";
            }
            echo "</tr>";
            for($i = 0; $i < count($row); $i++) {
                echo "<tr>";
                for($j = 0; $j < count($keys); $j++) {
                    echo "<td>" . $row[$i][$keys[$j]] . "</td>";
                }
                echo "</tr>";
            }
        }
        echo "</table>";
    } while($result->nextRowset());
    echo "</body></html>";
?>

The other code using the OCI PHP way is also not working with multiple statements:

$stid = oci_parse($conn, 'select * from all_objects; select * from all_users;');

The oci_parse just fails saying it doesnt understand the semicolon character.

Is there some way to run multiple statements in one batch simultaneously in PHP connecting to latest Oracle XE.

This question is different from the existing MySQL question as this question is about Oracle which is a different driver with different capabilities and limitations.

There are no errors the fetchAll returns a zero array no data. — , Feb 24 '17 at 01:04
Just curious, what's the problem with running your queries with separate calls? Why do you think it's necessary to stuff every query in the script in a single blob and then fire it against a database? — Your Common Sense, Feb 24 '17 at 06:09
Another reason is they might write a script they wanna send in PL/SQL which would be a bunch of statements that need to be run together. — , Feb 27 '17 at 08:45

score 0 · Answer 1 · answered Feb 24 '17 at 10:12

0

I'm actually surprised that it works with Microsoft SQL Server. PDO::prepare first parameter is a SQL statement as per the doc, not several ones.

The ability to put several statements in a prepare is a security concern for me. Even if it should be avoided as much as possible, it can happen that the string for the SQL query is dynamically built in PHP. In case of malicious attack and unescaped input, people could add a second statement after your select and delete data in your DB.

I'm also concerned about how PHP would handle the result of two SQL statements on a single call if the tables don't have the same structure. What should the result array look like?

TL;DR : Use multiple execute and fetchall or retrieve all your data in a single SQL query, using UNION ALL for instance.

answered Feb 24 '17 at 10:12

JeromeFr

1,869
2
17
20

1

Your musings are interesting but completely off the track and factually incorrect. Whether PDO supports multi-query statement, depends on the underlying implementation. – Your Common Sense Feb 24 '17 at 13:16
@YourCommonSense : There is no such thing as "Multi-query statement". A query is a SQL statement. So if the PDO doc says it takes *a* statement as the first parameter, it is clear that it doesn't support multiple statement at once. So per the specification it doesn't support it. If an implementation supports it, it doesn't respect the specs. – JeromeFr Feb 24 '17 at 13:38
If you want multiple statement, there as specific methods for that. For instance in mysql : http://php.net/manual/en/mysqli.multi-query.php . But the MySQL doc warns about SQL injections and people who could add malicious statements in your string : https://dev.mysql.com/doc/apis-php/en/apis-php-mysqli.quickstart.multiple-statement.html . It also says `Use of the multiple statement with prepared statements is not supported.` – JeromeFr Feb 24 '17 at 13:38
@YourCommonSense I'm the only one linking the doc here, you are only telling me I am wrong without proving it. So I give facts, you don't. https://www.w3schools.com/sql/sql_syntax.asp : "Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server." Which again means that having a string with more than one semicolon means it's more than one statement. And the doc says : *a* statement. Did you read it? – JeromeFr Feb 24 '17 at 14:37
The problem is that your links, as well as your fantasies are irrelevant to the question asked and should be posted as a comment – Your Common Sense Feb 24 '17 at 14:54

PHP PDO Oracle not working for multiple statements

1 Answers1