CODEWITHSUNDEEP
sqloracle-databasesecurity
Iam Riz
Iam Riz

Reputation: 127

What are the characters that need to be escaped in Oracle SQL to avoid SQL Injection?

just want confirming what are the characters that need to be escaped in Oracle SQL to avoid SQL Injection? So far, I only found this article that seems to answer my question: https://ss64.com/ora/syntax-escape.html. Yet, as I heard, the amphora '@' character also needs escaping, hence, I thought that above article has incomplete list of characters that needs escaping. If anyone could point me to the complete list of characters in Oracle SQL that needs escaping, then it will greatly be appreciated.

Upvotes: 1

Views: 1845

Answers (1)

MT0
MT0

Reputation: 167962

[TL;DR] Don't try to prevent SQL injection by escaping characters; instead don't use dynamic SQL or generate queries through string concatenation and use properly parameterised queries instead.

That linked page is not about escaping dynamic SQL it is about handling characters that have special meanings in literals (and in some cases its only for a sub-set of user interfaces).

If you have a string literal then this is defined by single-quotes ' around a string and if you want to use a single-quote in the string literal then you need to escape the single-quote by using a second single-quote.

SELECT 'The woman''s hat blew off in the wind.' FROM DUAL

In the SQL*Plus user interface, you can set the escape character used in LIKE queries so that the % or _ characters can be matched that would be otherwise treated as wildcards in the literal:

SET ESCAPE '\';
WITH data ( value ) AS (
  SELECT 'abc%def' FROM DUAL UNION ALL
  SELECT 'abcdef'  FROM DUAL
)
SELECT * FROM data WHERE value LIKE '%\%%';

However, you can specify the same thing in the query (which is valid in all user interfaces, not just SQL*Plus):

WITH data ( value ) AS (
  SELECT 'abc%def' FROM DUAL UNION ALL
  SELECT 'abcdef'  FROM DUAL
)
SELECT * FROM data WHERE value LIKE '%\%%' ESCAPE '\';

The third thing they mention is & is used for substitution variables. Many user interfaces do not support substitution variables so this is only a concern for those limited ones that do.

This is a valid SQL statement:

SELECT 'A&B' FROM DUAL

However, in SQL*Plus the ampersand & character signifies the start of a substitution variable and to ignore it you need to either: redefine the substitution variable's starting character; escape that character; or turn off scanning for substitution variables.

what are the characters that need to be escaped in Oracle SQL to avoid SQL Injection?

All characters are valid in SQL. SELECT '&@()''"%' FROM DUAL is a valid statement. It depends on the context of where they occur whether they will cause SQL injection issues.

Yet, as I heard, the ampersand '@' character also needs escaping

The ampersand & character only needs escaping when the user interface supports substitution variables and then only when you want to prevent it being used as a substitution variable; in other SQL user interfaces it has no special meaning.

The at @ character does not need escaping in SQL.

In general:

  • Do not try to escape characters to prevent SQL injection.
  • Do not build the query from concatenating strings.
  • Use parameterised queries and pass the values into them as bind variables.
  • If you have to dynamically provide a schema/table/column name then use DBMS_ASSERT to validate those object identifiers (and pass all other values as bind variables).

Upvotes: 4

Related Questions