Creating reusable SQL commands in c# programing

Question

I have following query that works.

string sqlCommandText = "SELECT * FROM Admin_T where AdminID =
'" +  textBox.Text + "'";

It is a fix command and I cannot use it with user given Table names and Column names at run time.

What I am actually trying to make is command like

string sqlCommandText = "SELECT * FROM Admin_T where 
'" + UserGivenColumnName + "' = '" + conditionTB.Text + "'";

"UserGivenColumnName" can be any column that is part of that specific table.

Trying to create flexibility so that same command can be used under different circumstances.

What stops you from having a string reference named `UserGivenColumnName` and assign it whatever value you want ? — Yassin Hajaj, Jan 24 '16 at 15:32
Is this just the simplified version of what you are really trying to do? Because I don't see a problem here in formatting a `string` using two variables.... side-note: watch out for sql injection when you create sql commands including user input! — René Vogt, Jan 24 '16 at 15:35
Make the field names a select control on your form. Then the user can only select valid fields and you only have to process another form field. — Dan Bracuk, Jan 24 '16 at 15:35
@BilalMirza You should avoid creating queries dynamically, [read more about SQLInjection](http://www.acunetix.com/websitesecurity/sql-injection/) — tchelidze, Jan 24 '16 at 15:36
Bad approach with query as concatenated string - target for SQL Injection. Even if `UserGivenColumnName` is getting from user input, then you cannot use parameters for column name.Consider [Entity Framework](https://msdn.microsoft.com/en-us/data/ef.aspx). — Fabio, Jan 24 '16 at 15:38
This question already has an answer here: http://stackoverflow.com/a/1246848/109122. — RBarryYoung, Jan 24 '16 at 19:10

score 0 · Answer 1 · answered Jan 24 '16 at 15:37

SqlCommand and none of related classes used by ADO.NET does not support such a functionality as far as I know.

Of course your should never build your sql queries with string concatenation. You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

But prepared statements only for values, not column names or table names. If you really wanna put your input string to your column name, create a whitelist and use it as a validation before you put it in your query.

http://codeblog.jonskeet.uk/2014/08/08/the-bobbytables-culture/

score 0 · Answer 2 · answered Jan 24 '16 at 15:45

0

I think an Object-Relational Mapper (ORM) is perhaps the droid you are looking for. Entity Framework might be a good place to start.

Please also do take the time to understand what SQL injection is, as the other users have also prompted you to.

answered Jan 24 '16 at 15:45

jjee

225
1
6

paparazzo · Answer 3 · 2016-01-24T18:54:05.390

It is not returning anything as it is just comparing two strings
With the 'UserGivenColumnName' it is a string comparison
And those two strings are not equal

You can do it (column) by just not including the '
But it is still a bad idea
SQLinjection is a very real and very bad thing

string sqlCommandText =   
"SELECT * FROM Admin_T where " + UserGivenColumnName + " = '" + conditionTB.Text + "'";

or

string sqlCommandText =   
"SELECT * FROM Admin_T where [" + UserGivenColumnName + "] = '" + conditionTB.Text + "'";

Creating reusable SQL commands in c# programing

3 Answers3