0

I'm currently using c# to connect to a linked SQL Server. The Problem is that parameterization does not work as I have to use openquery in the command SQL string.

This now means for me that (as far as I'm aware) I'm having the Problem that I need to do what shouldn't be done.....set Manual setps to avoid SQL injection instead of using parameterizied SQL commands.

What I have is the Parameters themselves and the values of them which I Need to make secure.

As an Example:

"SELECT * FROM OPENQUERY(LOCALSERVER, 'SELECT * FROM accounts where accountNumber=@accountNumber and Client=@client')"

The parameter accountnumber has the value 100-200-300-400 and the Client has the value 'MIP'.

So my question is: Is there any list of the most common attack routes and how to combat them?

(or is there any other route I can take as the most common route is not open for me?)

Thomas
  • 2,886
  • 3
  • 34
  • 78
  • 7
    Better to include that query (for which you cannot use parameters) in question. – Evk Mar 15 '18 at 08:34
  • so prepared statements wouldn't work for you? – Stender Mar 15 '18 at 08:40
  • https://en.wikipedia.org/wiki/Prepared_statement – Stender Mar 15 '18 at 08:41
  • Possible duplicate of "Execute stored proc with OPENQUERY " https://stackoverflow.com/questions/4159465/execute-stored-proc-with-openquery – Wheels73 Mar 15 '18 at 08:44
  • @evk I added an example let me know if it is enough there else I will also include code. In essence ist more than just that one.....ist a dozen different tables where I can have selects for. Only "positive" is ONLY selects are possible. – Thomas Mar 15 '18 at 08:59
  • @Wheels73 from what I saw the question and answer there are very different. Why duplicate? (maybe I'm overlooking somethhing there) – Thomas Mar 15 '18 at 08:59
  • @Thomas - I said "Possible" – Wheels73 Mar 15 '18 at 09:01
  • @wheels no Problem just wanted to verfiy if I had overlooked something there (if so I would flag my question myself as a duplicate of it) – Thomas Mar 15 '18 at 09:02
  • @Thomas - That's ok. I originally just said "has this been answered here". Then I thought.. oh.. is this a duplicate.. that's all. Hope you get it sorted. – Wheels73 Mar 15 '18 at 09:07
  • Can you use sp_executesql on linked server instead of OPENQUERY? – Evk Mar 15 '18 at 09:16
  • @evk sadly nope I HAVE to use openquery (don't have control over the SQL Server myself and the SQL admins only offer that Option) – Thomas Mar 15 '18 at 09:18
  • Well then you are out of luck I guess and indeed have to manually validate your inputs. I'd use whitelist regular expressions for that. For example accountNumber has specific pattern (3 digits, hypen, 3 digits ...), that's easy. Client name probably can contain only alphabet characters and numbers - also not hard. And so on - each input should be validated separately, based on type of information expected to be there. – Evk Mar 15 '18 at 09:31

1 Answers1

4

From https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet.

You have some following options:

Option 1: White List Input Validation

Please go to https://www.owasp.org/index.php/Input_Validation_Cheat_Sheet to see the suggest white list.

Various parts of SQL queries aren't legal locations for the use of bind variables, such as the names of tables or columns, and the sort order indicator (ASC or DESC). In such situations, input validation or query redesign is the most appropriate defense. For the names of tables or columns, ideally those values come from the code, and not from user parameters. But if user parameter values are used to make different for table names and column names, then the parameter values should be mapped to the legal/expected table or column names to make sure unvalidated user input doesn't end up in the query. Please note, this is a symptom of poor design and a full re-write should be considered if time allows. Here is an example of table name validation. 

 String tableName;
 switch(PARAM):
   case "Value1": tableName = "fooTable";
                  break;
   case "Value2": tableName = "barTable";
                  break;
     ...
   default      : throw new InputValidationException("unexpected value provided for table name");

Option 2: Escaping All User-Supplied Input

This technique should only be used as a last resort, when none of the above are feasible. Input validation is probably a better choice as this methodology is frail compared to other defenses and we cannot guarantee it will prevent all SQL Injection in all situations.

This technique is to escape user input before putting it in a query. It is very database specific in its implementation. It's usually only recommended to retrofit legacy code when implementing input validation isn't cost effective. Applications built from scratch, or applications requiring low risk tolerance should be built or re-written using parameterized queries, stored procedures, or some kind of Object Relational Mapper (ORM) that builds your queries for you.

This technique works like this. Each DBMS supports one or more character escaping schemes specific to certain kinds of queries. If you then escape all user supplied input using the proper escaping scheme for the database you are using, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding any possible SQL injection vulnerabilities.

The OWASP Enterprise Security API (ESAPI) is a free, open source, web application security control library that makes it easier for programmers to write lower-risk applications. The ESAPI libraries are designed to make it easier for programmers to retrofit security into existing applications. The ESAPI libraries also serve as a solid foundation for new development.

Additional actions: Least Privilege with Multiple DB Users and Views

Red Wei
  • 854
  • 6
  • 22