B4J Question How to avoid SQL injection in SQL without question marks?

[MathiasM]

Hello

I have SQL queries assembled by user inputted(?) data, ofcourse I want to avoid SQL injections.
Normally I would do something like this=

SELECT * FROM Users WHERE Username = ?

And then pass parameters to replace the "?".

But now, in this case, "Username" is also a variable passed by the user (coming from a filter query parameter in a REST api)

I tried this:

SELECT * FROM Users WHERE ? = ?

And then passing "Username" and the the actual Username as parameters. However, the SQL class doesn't seem to support this as the resultset is closed after trying to read data out of it.

How can I safely escape the string "Username" to be used in a WHERE clause and then pass it without SQL injection possibility?

Thanks a bunch!

 

[tchart]

There is some good discussion on stackoverflow on this topic. The consensus was that there is no 100% way to detect it.

In one of my products I have the ability for administrators to write ad-hoc SQL queries for reporting purposes. So avoid any nasty command (delete, drop etc) I have a function that checks whether a statement is a select only statement. Again not 100% reliable but better than nothing. Happy to share this if you want.

For your problem there is also issues with selects within selects which can also leads to data leaks.

 

[tchart]

Also watch this video, very interesting what can be achieved.

 

[Erel]

How can I safely escape the string "Username" to be used in a WHERE clause and then pass it without SQL injection possibility?

Dim AllowedColumns As B4XSet = B4XCollections.CreateSet2(Array("Username", "SomeotherField", "AndAnother"))

If AllowedColumns.Contains(UserInput) = False Then
    'reject query
End If

 

[LucaMs]

Dim AllowedColumns As B4XSet = B4XCollections.CreateSet2(Array("Username", "SomeotherField", "AndAnother"))

If AllowedColumns.Contains(UserInput) = False Then
    'reject query
End If

I would have used a List because... I fell behind ??

 

[Erel]

Assuming that there aren't many items then a List will work just as fine (but it is a bit less elegant).

 

[LucaMs]

(but it is a bit less elegant)

It would be less modern

 

[MathiasM]

Dim AllowedColumns As B4XSet = B4XCollections.CreateSet2(Array("Username", "SomeotherField", "AndAnother"))

If AllowedColumns.Contains(UserInput) = False Then
    'reject query
End If

Simple, yet elegant. This is the way I'm going to fix it! Thanks.

 

[Erel]

Better to lowercase the names in the B4XSet and test UserInput.ToLowerCase.