-1

I am exposing a web service that constructs a SQL SELECT statement and accepts parameters from a controller. The generic functions looks like this:

DataTable SqlSelect (string select, string from,  string Where, string orderby = "", string groupBy = "")
 {
     string sql = "SELECT " + select + " FROM " + from + " WHERE " + where
     (orderby =="") ? "" : "ORDER BY " + orderby ...
     //do other stuff
 }

Now what makes me worry is the fact that base on given function above, the user may now inject harmful commands like:

SqlSelect("DROP TABLE 'TABLENAME'", "INFORMATION_SCHEMA.TABLES", "TABLE_NAME like %%'");

Which I want to prevent.

Now my question is: what is the best thing I can do to prevent user to UPDATE, MODIFY, DELETE, TRUNCATE tables and only allow SELECT statement (can use something like READ oNLY?)

Note: this is similar to this question but the user was working on PHP, while I'm in ASP.NET MVC, also what I want to achieve here is only allow SELECT or 'GET' statement.

Community
  • 1
  • 1
dr.Crow
  • 1,493
  • 14
  • 17
  • Possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Brandon Moretz Nov 26 '15 at 03:30
  • @BrandonMoretz Sir, might you not consider, the user was asking about PHP Framework. and so solutions provided there was also suggesting to use PHP Plugins – dr.Crow Nov 26 '15 at 05:23
  • 1
    One method is to ensure that the account connecting to the database is read only. Then you're only limited to DOS type attacks. And you also have to ensure that that account stays read only. But in the end this is a bad idea. – Nick.Mc Nov 26 '15 at 23:07
  • Thanks, I already found the solution. And yes my first concept was really a bad idea – dr.Crow Nov 27 '15 at 01:51
  • @dr.Crow, consider posting your solution so others who come upon this post may be enlightened. – Nick.Mc Nov 30 '15 at 02:08

2 Answers2

1

Do not do it this way. You need to parameterize your queries which means that you cannot accept SQL text as an input. I have found many attempts by developers to detect SQL injection attacks and I almost always find a way of getting past their logic.

If you need to be able to dynamically construct any SELECT query based on any table in your database then you could easily create a class that indicates the table, select columns and where predicate columns as enums, and the values of the where predicates. Concatenate the SQL text based on this class and include the predicate values using SqlParameters.

It is just one example, but for sure you do not want to accept SQL text.

Vanlightly
  • 1,389
  • 8
  • 10
  • Yes, It's actually somehow what I did, I will post my solutions afterwards (if i'm not busy). Thanks any way. – dr.Crow Nov 27 '15 at 01:49
0

Make use of a data reader https://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx. You can catch any exceptions when you call ExecuteReader().

However I would advise against exposing this kind of generic functionality to client side code. You should rather only provide controlled access to your data via an appropriate data layer using something like the repository pattern.

TheEdge
  • 9,291
  • 15
  • 67
  • 135
  • Does this data reader means I cannot execute other commands? Would it be possible to use Reader to update Records? I read the documentation but it doesn't provide the exact details of my concern – dr.Crow Nov 26 '15 at 05:21