CODEWITHSUNDEEP
c#sqlasp.net-coredappersqlkata
Tufy Duck
Tufy Duck

Reputation: 110

How to protect against sql injection when using SqlKata?

I came across some unsafe SqlKata functions:

Example

public class TestRepository
{
    private readonly QueryFactory _queryFactory;

    public TestRepository(QueryFactory queryFactory)
    {
        _queryFactory = queryFactory;
    }

    public IList<TestResult> TestFunction(string value)
    {
        var query = _queryFactory.Query("MyTableName");
        query.Where("MyColumnName", value);
        return query.Get<TestResult>();
    }
}

and output:

--> output --> sqlkata query log
--> select * from MyTableName where MyColumnName = 'Select Id from Users where Nickname = ''Admin'''

I have to use SqlKata and asp.net core. I need a solution.

Upvotes: 2

Views: 3603

Answers (2)

amd
amd

Reputation: 21472

SqlKata by default is safe and protect you from SQL injection if you follow the recommendation.

a. Use the native method to build the query as suggested by @bradbury9

var name = "unsafe string value here";
query.Where("Name", name);

b. When using the Raw methods make sure you use the parameter placeholder

var name = "unsafe string value here";
query.WhereRaw("[Name] = ?", name);

c. and the most important one, is to avoid using the SqlResult.ToString() to execute your queries.

The provided example is totally safe since SqlKata use parameter binding technique, here the logger do some extra work by replacing the actual value by their placeholders to show you the final result.

Upvotes: 3

Cleptus
Cleptus

Reputation: 3541

The safest way to avoid SQL Injection is using parametrized queries.

As it is says the SQLkata documentation in this link it does use mostly parameters to build the SQL executed. In that same link there are some functions that do NOT use parameters.

Because there are more safe functions rather than unsafe functions, I am copy-pasting the functions names that are documented to not use parameters in the documentation:

  • WhereNull
  • WhereTrue
  • WhereFalse

As a side note, if you are working againt a database that has a profiler, you can monitor what actual SQL is executed in the database and check if parameters where used. That is useful when working with closed source code components. For example in SQL Server you have the SQL Server Profiler and the SQL Server monitor (integrated in the SQL Server Management Studio)

Upvotes: 3

Related Questions