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

MathiasM said:

Hello

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

SQL:

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:

SQL:

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!

Click to expand...

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

MathiasM said:

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

Click to expand...

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

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

Erel said:

B4X:

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

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

Click to expand...

Erel said:

(but it is a bit less elegant)

Click to expand...

Erel said:

B4X:

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

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

Click to expand...