//---------------------------------------------------------------------
/// <summary>
/// Adds a SQL parameter to the SQL command object managed by this
/// class instance
/// </summary>
/// <param name="Name">Parameter name</param>
/// <param name="Type">Parameter type</param>
/// <param name="Size">Parameter size</param>
/// <param name="Value">Parameter value</param>
/// <param name="Direction">Parameter direction</param>
/// <returns>
/// On success, returns the SqlParameter object added, null
/// otherwise
/// </returns>
//---------------------------------------------------------------------
public TParameter AddParameter(String Name, SqlDbType Type, int Size,
Object Value, ParameterDirection Direction)
{
// Convert the SqlDbType to a generic DbType
DbType DbTypeToUse;
if (!AntiSQLiCommon.ConvertToDbType(Type, out DbTypeToUse))
{
return(default(TParameter));
}
// Add the parameter
try
{
// Set the name, type and size
//SqlParameter CurrentParameter = new SqlParameter(Name, Type, Size);
TParameter CurrentParameter = new TParameter();
CurrentParameter.ParameterName = Name;
CurrentParameter.DbType = DbTypeToUse;
CurrentParameter.Size = Size;
// Set direction
CurrentParameter.Direction = Direction;
// Set value, only if it's an input or inputoutput parameter
if ((Direction == ParameterDirection.Input) ||
(Direction == ParameterDirection.InputOutput))
{
CurrentParameter.Value = Value;
}
// Add the parameter to the main sql command object
SqlCommandObject.Parameters.Add(CurrentParameter);
// Return the current parameter object in case the
// caller wants reference to it
return(CurrentParameter);
}
catch (Exception)
{
return(default(TParameter));
}
}
//---------------------------------------------------------------------
/// <summary>
/// Creates and parameterizes an instance of Microsoft.Azure.Cosmos.QueryDefinition
/// using the given queryText and args to mitigate risk from SQLi attacks
/// </summary>
/// <param name="queryText">Query text to use</param>
/// <param name="args">Arguments to map and parameterize</param>
/// <returns>
/// Returns a QueryDefinition instance that is been instantiated
/// with the given queryText and parameterized
/// </returns>
//---------------------------------------------------------------------
public static QueryDefinition Create(String queryText, params Object[] args)
{
// Input validation
AntiSQLiCommon.ValidateQueryTextAndArgsThrowEx(queryText, args);
// Parameterize given arguments
IEnumerable <Documents.SqlParameter> parsedParameters;
if (!AntiSQLiCommon.TryConvertObjectsToDbParameterCollection(out parsedParameters, args))
{
throw new AntiSQLiException(Resources.AntiSQLiResource.Error_UnableToParseArguments);
}
// If no parameters parsed, then stop execution, may not be safe to continue
if (parsedParameters.Count() == 0)
{
throw new AntiSQLiException(Resources.AntiSQLiResource.Error_NoParametersParsed);
}
// Substitute the queryText formatters (i.e. {0} .. {N}) with the
// proper parameter names (i.e., @AntiSQLiParam1 ... @AntiSQLiParamN,
// where N is the number of elements in the args params
String parameterizedQueryText;
if (!AntiSQLiCommon.TryParameterizeQueryText(queryText, parsedParameters, out parameterizedQueryText))
{
throw new AntiSQLiException(Resources.AntiSQLiResource.Error_UnableToParameterizeQuery);
}
// Create final QueryDefinition object and add parameters
QueryDefinition finalQueryDefinition = new QueryDefinition(parameterizedQueryText);
foreach (var p in parsedParameters)
{
finalQueryDefinition = finalQueryDefinition.WithParameter(p.Name, p.Value);
}
return(finalQueryDefinition);
}
/// <summary>
/// For Microsoft.Azure.Documents.SqlQuerySpec https://docs.microsoft.com/fr-fr/dotnet/api/microsoft.azure.documents.sqlqueryspec?view=azure-dotnet
/// </summary>
/// <param name="sqs"></param>
/// <param name="queryText"></param>
/// <param name="queryTextArgs"></param>
public static void LoadQuerySecure(this SqlQuerySpec sqs, String queryText, params Object[] queryTextArgs)
{
AntiSQLiCommon.ParameterizeAndLoadQuery(sqs, queryText, queryTextArgs);
}
//---------------------------------------------------------------------
/// <summary>
/// Extension to System.Data.SqlClient to load query with untrusted
/// data provided in args parameters safely to mitigate the risk from
/// SQL injection attacks
/// </summary>
/// <param name="cmd"></param>
/// <param name="queryText">Query string to execute</param>
/// <param name="queryTextArgs">Parameters</param>
//---------------------------------------------------------------------
public static void LoadQuerySecure(this System.Data.SqlClient.SqlCommand sqlCommandObj, String queryText, params Object[] queryTextArgs)
{
AntiSQLiCommon.ParameterizeAndLoadQuery <System.Data.SqlClient.SqlParameter>(sqlCommandObj, queryText, queryTextArgs);
}
