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.

Hi,

I am using BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList  actions for in clause. Will it help in preventing sql injection?