CODEWITHSUNDEEP
phpsqloracle-database
cytsunny
cytsunny

Reputation: 5030

Is there a built in function to escape string for Oracle query? (like mysql_real_escape_string for MySQL)

I understand that the proper way to handle all SQL query nowadays should be using PDO (or use the function provided in a PHP framework, like eloquent in laravel) However, as there is mysql_real_escape_string for MySQL, I am curious if there is a function like that for Oracle in PHP?

I have searched on Google and most answer seems to be writing a string replace code by yourself. That works at least on some common problem like quote ('), but I am just curious if there are built-in function for that. (and just in case there are other escape needed to be done to prevent SQL injection)

Upvotes: 2

Views: 3103

Answers (2)

Christopher Jones
Christopher Jones

Reputation: 10496

To answer your real question about SQL injection you should (in fact 'must') use bind variables instead of string concatenation, as mentioned by Álvaro González. This is also important for performance and scalability. Binding keeps the data separate from the SQL, and avoids the strict need to sanitize data. To use an example I have laying around:

<?php

$conn = oci_connect('hr', 'welcome', 'localhost/orclpdb');

$query = "select first_name, last_name
          from employees
          order by
              case :ob
                   when 'FIRST_NAME' then first_name
                   else last_name
              end";

$vs = "FIRST_NAME";

$s = oci_parse($conn, $query);
oci_bind_by_name($s, ":ob", $vs);
$r = oci_execute($s);

echo "<table border='1'>\n";
while (($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "  <td>".($item !== null ? htmlentities($item, ENT_QUOTES):"&nbsp;")."</td>\n";
    }
    echo "</tr>\n";
}

Upvotes: 2

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

There is a package DBMS_ASSERT. It is dedicate to prepare and validate input string. DBMS_ASSERT
ENQUOTE_LITERAL function

If want to hardcore string literals you can use quitting mechanism.
q'( ... )'
q'~ ... ~'
q'! ... !'

DECLARE
       v VARCHAR2(1024);
    BEGIN
       v := q'( The string  with quouts ',','   )';
       DBMS_OUTPUT.PUT_LINE(v);
    END;

Upvotes: 0

Related Questions