Does this allow SQL injection?

Does this allow SQL injection?

  

Hi Peter,

Yes it does. Look at this example https://stackoverflow.com/questions/15579898/mysql-injection-by-like-operator

Regards,

Marcelo

OS does not translate the name_search field into a bind parameter behind the scene?

Hi,

If you use an aggregate Outsystems takes care of that for you. But if you use an advance query you will get a warning stating that you should use encode sql

Regards, 

Marcelo

Solution

Hello Peter,

I'm not a security expert but allow me to share my thoughts on the matter:

If you are using an Aggregate or if you are using an advanced SQL query and your query parameter is not expanded inline your query should (as far as I know) be safe from SQL injection. Check out the "Use SQL Parameters for Protection" section of this w3school page on SQL injection to learn more about how SQL query parameters are handled and how they help preventing SQL injection.

If you are using an advanced SQL query with a query parameter that is expanded inline and if you assign a Variable to that parameter you will get a warning message just like Marcelo said.
You get this message because when your query parameter is expanded inline it will not be treated as a SQL parameter and it will be included in the SQL statement/query without first being evaluated. This means that you will be able to insert SQL clauses in your parameter and if you do not take certain precautions end-users may be able exploit it.

The warning message documentation page has some recommendations on how to prevent SQL injection attacks depending on the contents of the query parameter.

Just a final note on the EncodeSql() built-in function and when/how to use it:
When to use it: Use this function with advanced SQL queries and only if your query parameter contains an SQL clause and a distrusted variable (like user input) that should include only string literals (or in other words, if your users are only supposed to input text strings).

parameter  = "WHERE {entity}.[attribute] LIKE" + " ' " + EncodeSql(variable) + " ' "

How to use it: Wrap any distrusted variables (like user input) with the EncodeSql() built-in function.
This function only escapes certain characters (like single quotes, ') so it is paramount that you do not forget to wrap the EncodeSql() itself in single quotes, ', otherwise your query will still be vulnerable to SQL injection.
Something like this:

" ' " + EncodeSql(variable) + " ' "

where variable is the user input.

Solution

Joao's answer is the correct one.

J.Ja

IF i recall well, sometimes using the encodesql turns out the sql code with problems or not working at all. anyone has expericenced such issue when wrap a sql script with encodeSQL? 

Hello Jorge,

I don't know if I understood your question/statement correctly, but if you are using EncodeSql() around all of your SQL statement it can "break" it since it will escape any single quotes.

Let's say you have the following SQL statement:

SELECT {entity}.[attribute] 
FROM {entity}
@parameter

If you incorrectly wrap all of your parameter with EncodeSql():

parameter = EncodeSql("WHERE {entity}.[attribute] LIKE" + " ' " + variable + " ' ")

Let's say variable= "durian" 
You will get something like the following:

SELECT {entity}.[attribute] 
FROM {entity}
WHERE {entity}.[attribute] LIKE ''durian''

Note that the ''durian'' has two single quotes before and after it, not one double quote before and one after.
This SQL statement will not work.

You should only use EncodeSql() around variables that should be evaluated to a string literal, you should not use it around whole SQL clauses or statements.

Hopefully I did not misinterpret your post and I managed to help,
Kind regards