CODEWITHSUNDEEP
phpsqloracle-databasedoctrine-ormdbal
thed0ctor
thed0ctor

Reputation: 1396

How do you escape oracle reserved keywords when using Doctrine DBAL to execute queries on an Oracle database?

I'm trying to use Doctrine DBAL to execute the following query on an Oracle database. I've executed other queries before so it's not a connection issue. The problem stems from trying to escape the reserved word Number. I need to select a column as Number for later logic down the road. The simplified working version of the query is as follows: 

SELECT instructor.SecNum AS "Number"
      FROM (
           SELECT dbA.ID,
                  dbA.ClsNumber as SecNum,
                  dbA.CrsCatlgNbr,
                  ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA.CrsCatlgNbr) AS rn
           FROM dbA
           WHERE (dbA.SubCd = '5')
           AND (dbA.ID IS NOT NULL)
           ) instructor,
           (
            SELECT dbB.SecNum
            FROM dbB
            WHERE (dbB.SubCd = '5')
           ) student
      WHERE (instructor.rn = 1)
      AND (instructor.SecNum = student.SecNum);

The PHP code is as follows

// set up connection stuff and relevant variables
$sectionTable = array();
$dss1prdDatabase->executeQuery(
"SELECT instructor.SecNum AS \"Number\"
      FROM (
           SELECT dbA.ID
                 dbA.ClsNumber as SecNum
                 dbA.CrsCatlgNbr
                 ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA   CrsCatlgNbr) AS rn
           FROM dbA
           WHERE (dbA.SubCd = '5')
           AND (dbA.ID IS NOT NULL)
           ) instructor,
           (
            SELECT dbB.SecNum
            FROM dbB
            WHERE (dbB.SubCd = '5')
           ) student
      WHERE (instructor.rn = 1)
      AND (instructor.SecNum = student.SecNum);", $sectionTable);

When I do this I get an ORA-00911: invalid character error.

I've also tried preparing the statement first and then binding the escaped column name to the query:

// ...
$ESCAPEDNUMBER = '\"Number\"';
$sql = "SELECT instructor.SecNum AS ?
      FROM (
           SELECT dbA.ID
                 dbA.ClsNumber as SecNum
                 dbA.CrsCatlgNbr
                 ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA   CrsCatlgNbr) AS rn
           FROM dbA
           WHERE (dbA.SubCd = '5')
           AND (dbA.ID IS NOT NULL)
           ) instructor,
           (
            SELECT dbB.SecNum
            FROM dbB
            WHERE (dbB.SubCd = '5')
           ) student
      WHERE (instructor.rn = 1)
      AND (instructor.SecNum = student.SecNum);"
$stmt = $dbalDatabaseConnection->prepare($sql);
$stmt->bindvalue(1, $ESCAPEDNUMBER);
$stmt->execute($sectionTable);

But this gives me a different error: ORA-00923: FROM keyword not found where expected.

The ORA-00923 error is also found when I say $ESCAPEDNUMBER = addslashes('"Number"');

I have no idea what the issue is. Any ideas?

Again my question is how to properly escape oracle reserved words in php queries using Doctrine DBAL. I use quotes because in Oracle quotes are required in the query otherwise you'll get a ORA-00923: FROM keyword not found where expected" error.

Relevant documentation on DBAL can be found here And also

Upvotes: 0

Views: 854

Answers (1)

thed0ctor
thed0ctor

Reputation: 1396

Got it. The issue was the semi-colon at the end of the query. This works fine in the console but apparently gives the ORA-00911: invalid character when ran with DBAL. Go figure...

Fixed query:

// set up connection stuff and relevant variables
$sectionTable = array();
$dss1prdDatabase->executeQuery(
"SELECT instructor.SecNum AS \"Number\"
      FROM (
           SELECT dbA.ID
                 dbA.ClsNumber as SecNum
                 dbA.CrsCatlgNbr
                 ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA   CrsCatlgNbr) AS rn
           FROM dbA
           WHERE (dbA.SubCd = '5')
           AND (dbA.ID IS NOT NULL)
           ) instructor,
           (
            SELECT dbB.SecNum
            FROM dbB
            WHERE (dbB.SubCd = '5')
           ) student
      WHERE (instructor.rn = 1)
      AND (instructor.SecNum = student.SecNum)", $sectionTable);

Upvotes: 2

Related Questions