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!

 

[sfsameer]

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!

Hello,

The best way to prevent SQL Injections is using MS SQL Stored Procedures.

In MS SQL Stored Procedures there is a built-in function which executes the SQL script dynamically and that gives you an advantage to escape any SQL Injection.

Based on the above post, below is an example on how to use it in MS SQL Stored Procedures :

Create PROCEDURE SP_UsersSearch
(
@filtername nvarchar(255),
@searchtext varchar(400)
)
as

DECLARE @sql nvarchar(4000)

SELECT @sql = ' SELECT * from Users  Where '

IF @searchtext IS NOT NULL

SELECT @sql = @sql + @filtername + ' = @searchtext'

EXEC sp_executesql @sql, N'@searchtext varchar(400)',@searchtext

Output :
If the user inputs the information correctly then the output will be :

But if the user tried to SQL Inject it then the output will be :

Results :
it will stop the injection immediately

*Please note that there are a lot of great ways to prevent it, for example User Input validations which means you should search and replace special characters when he/she submits the request.


Thank you,
Saif

 

[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.