CODEWITHSUNDEEP
c#sql
MaxOvrdrv
MaxOvrdrv

Reputation: 1916

C# Parse SQL statement to use parameters

Dilemma: I have a need to build an API into another application. In this API, we must allow the developers to run SQL statements on the SQL server... Unfortunately, we are very restricted (yes, the world is upside down here) as to what we can do to minimize SQL injections... We can't create SPs on the SQL server and we can't restrict what the user can enter for his/her query either. Now while I was told that because we are building an API for "other developers", we shouldn't have to worry about SQL injection, I don't tend to agree and I'd really like to prevent that if I can...

So I was thinking that what I could do, and this is where my question comes in, is parse the query to either:

Are these, given my situation, the only 2 options I have? And if so, how would you implement number 2 above so that this example statement:

SELECT * FROM Table WHERE Field1='test' AND Field2=1

Becomes:

SELECT * FROM Table WHERE Field1=@Field1 AND Field2=@Field2

Where the parameters have been extracted dynamically in C#? And if so, how would I be able to extract the data type for the params? Is that possible?

Upvotes: 0

Views: 2326

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294277

You can't solve it at the application side. You can restrict as much as you can, and parse all you want, but the SQL injection attacks are contiguously evolving and new vectors are being created that will bypass your parsing.

For running ad-hoc queries I strongly recommend relying on permissions, not on SQL parsing. Users should be able to inject themselves all they want, but the permissions should prevent any damage. You won't be able to prevent (intentional or accidental) DOS from running a bad query that brings the server to its knees, but for that there is resource governance and audit.

But I can't stress this enough: you won't parse yourself out of the problem. Multi-byte character exploits are +10 years old now, and I'm pretty sure I don't know the majority of filter/parse by-pass techniques out there.

Upvotes: 2

Farrokh
Farrokh

Reputation: 1167

you can extract parameters from sql syntax 

SqlConnection sqlCon = new SqlConnection("...");
String sqlScript = "Somethings ...";
Regex r = new Regex(@"(?<Parameter>@\w*)", RegexOptions.Compiled);
string[] parameters = r.Matches(sqlScript).Cast<Match>().Select<Match, string>(x => x.Value.ToLower()).Distinct<string>().ToArray<string>();
SqlCommand sqlCom = new SqlCommand(sqlScript, sqlCon);
foreach (string sqlParam in parameters)
{
    sqlCom.Parameters.AddWithValue(sqlParam, "PARAMETER VALUE");
}

Upvotes: 0

Zache
Zache

Reputation: 1043

If your assignment is just writing a wrapper around a database so that other developers can send in their own SQL and get results then SQL injections are the "normal use case". There is just no way of knowing if a request is malicious or not. If you are allowed to run "good" code, you'll always be able to run "evil" code.

Upvotes: 1

Related Questions