Eliad Ayehu Eliad Ayehu - 2 months ago 7
C# Question

Prevent sql injection in dynamic code

I need to execute dynamic string with sql query from C# code and prevent sql injection.

my code is like that:

internal static string Get_Running_Cars(string from, string to)
{
return string.Format(
" declare @from as datetime = '{0}'" +
" declare @to as datetime = '{1}'" +
" select top 3 DATEDIFF(second,starttime,endtime) as sum,carname as name" +
" from cars" +
" where @from < starttime and @to > endtime ", from, to
);
}


in that code I can insert malicious code into those string.

how can I use it safely ?
should I prevent those chars :
'
,
;
,
,
-
?

Answer

I use EF, and therefore the best answer is to use 'SqlParameter'.

I build this object :

public class SqlQueryEntity
    {
        public string query { get; set; }
        public object[] _params { get; set; }
    } 

and send it with DbRawSqlQuery.

I need to insert query with array of 'object' which contains :

new SqlParameter[] { 
                    new SqlParameter("from", from),
                    new SqlParameter("to", to)
                }

'to' and 'from' can contains Datetime object.