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

MathiasM

Active Member
Licensed User
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!
 

sfsameer

Expert
Licensed User
Longtime User
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!
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 :
SQL:
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 :
1624522322075.png


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

1624522353289.png


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
 
Upvote 0

tchart

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
How can I safely escape the string "Username" to be used in a WHERE clause and then pass it without SQL injection possibility?

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

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

LucaMs

Expert
Licensed User
Longtime User
B4X:
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 😁:(😢
 
Upvote 0

MathiasM

Active Member
Licensed User
B4X:
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.
 
Upvote 0
Top