Use case of EncodeSQL

I just want to know the exact behavior of EncodeSQL. As per this link, It has got only one example where the special character ' is replaced by ' '

EncodeSql("another ' test") = "another '' test"

How EncodeSQL will help prevent SQL injection ?

Apart from replacing the single quote, does it have any other behavior ?

I am using the following query with a text parameter having expand inline set as YES.

I am encoding the parameter by EncodeSQL()

Now, at front end, i am passing the SQL injection as follows : This is allowing the injection.

Need to know, what is the exact purpose of EncodeSQL() function ?

Hello Debasis,

EncodeSQL will do what is explained in the description: It will translate special characters in your string, in order to you to be able to use those characters in the SQL.

EncodeSQL is not meant to prevent SQL Injection.

If you want to use a LITERAL in your query, coming from the user, you should use the VerifySQLLiteral, to guarantee that the input/string is a valid LITERAL and do not contain anything else.

https://success.outsystems.com/Documentation/10/Reference/Errors_and_Warnings/Warnings/SQL_Injection_Warning

Cheers.

Thank you Eduardo for the reply.

If you check the link mentioned here about EncodeSQL, it is saying "Using expand inline parameters without encoding distrusted variables (e.g. user input) compromises the database security by allowing SQL injection.You should use this function when managing in-line parameters in your SQL query."

This clearly says that without encoding there is possibility of SQL injection. But if we use Encodesql then this can be prevented. But this is not happening.

Hello Debasis,

This documentation is not correct, as EncodeSQL (and the name of the function is a hint of what it really does), does not prevent SQL Injection.

To prevent it, you have to use the VerifySQLLiteral to guarantee that the value being passed is a LITERAL value, and not an SQL statement.

Cheers.

Hello Debasis,

Let me just add that EncodeSQL can also help prevent SQL injection in SQL string literals. It escapes the single quotes, which guarantees that an end-user cannot close the starting single quote and then add more SQL afterwards. But for this to work, you must enclose the @param in single quotes.

Hopefully an example will make this clear.

Suppose that a malicious end-user enters the following input to try to close the current SQL clause and then delete all the records of an entity:

''; DELETE FROM {entity};

After you run this input string through SqlEncode(), the two single quotes are escaped and it becomes:

''''; DELETE FROM {entity};

So far so good,

Now, if in your SQL you don't enclose your parameter in single quotes:

SELECT {entity}.* FROM {entity} WHERE {entity}.[textattribute] like @parameter

...then the SQL query sent to the database will still allow the SQL injection:

SELECT {entity}.* FROM {entity} WHERE {entity}.[textattribute] like ''''; DELETE FROM {entity};

To fix this and ensure that no SQL injection is possible, you must enclose in single quotes the parameter that will be substituted by the string input from the end-user, like this:

SELECT {entity}.* FROM {entity} WHERE {entity}.[textattribute] like '@parameter'

Now, when the parameter is substituted, it will look like:

SELECT {entity}.* FROM {entity} WHERE {entity}.[textattribute] like '''''; DELETE FROM {entity};'

As you can see, as long as you enclose the string parameter in single quotes and you also use the EncodeSql() built-in function, it is now impossible for an end-user to inject SQL.

We will make this more explicit in the documentation very soon, thank you Debasis and also to Eduardo for pointing this out.