0

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:

  • Check for SQL Injection patterns and return an error if found; or
  • Remove any "assignment" sections, and replace then with parameters dynamically

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?

MaxOvrdrv
  • 1,780
  • 17
  • 32
  • can you create a view? and a separate db schema? – Andy May 26 '14 at 13:38
  • 1
    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 the "normal use case". There is just no way of knowing if this request is malicious and that another is not given your description. – Zache May 26 '14 at 13:38
  • 1
    To make sense of SQL passed to you you'd have to parse it and get some sort of data structure with parsed SQL. And that's not trivial work. Maybe you can send the SQL to DB and DB can do that for you and return parsed data back to you because DB has that parser thing built inside. But your performance would suffer. So, if this is for your internal developers and you have been told they will take care of handling SQL injection I think your best option is to leave that responsibility to them. Because you handling this seems too complicated and error prone. – pero May 26 '14 at 13:55
  • @PetarRepac and Zache : Thanks... that's what i thought (leave it to the devs not to dump a table or something), but i thought there might be other ways of checking this stuff out. If Zache want's to post his comment as an answer i will accept it (first one to post the comment). Thanks again! :) – MaxOvrdrv May 26 '14 at 14:15
  • @Zache See comment above to get points... just re-post your comment. – MaxOvrdrv Aug 07 '14 at 19:26

3 Answers3

2

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.

Community
  • 1
  • 1
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
1

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.

Zache
  • 1,023
  • 6
  • 14
0

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");
}
Farrokh
  • 1,167
  • 1
  • 7
  • 18