Im trying to use MySQL select command with 2 variables in c# but It only works with one variable.
My goal is to create a login system which uses a MySQL table with 3 columns: UserName , Password and TableName. Then I want to go to another table (with TableName as the table name) and in that table there is information about people which I display in WPF ListBox.
return connection.Query<Person>($"select * from "+ TableName +" where PhoneNumber LIKE "+ searchData and id > 0 order by Name").ToList();
I use this code to search the list by phone number.
TableName and searchData are the variables.
This is my code which doesnt work.
That isn't parameterized at all; parameters are specific SQL concepts, so you might have:
return connection.Query<Person>(
$"select * from {TableName} where PhoneNumber LIKE @searchData and id > 0 order by Name",
new { searchData }).AsList();
(or :searchData
, etc, depending on the variant of SQL)
This parameterizes the searchData
correctly.
Note, however, that table names cannot be parameterized; in general you should avoid needing to inject a table name into SQL, but when you need to do this, you will have to concatenate it much like you are already. It would be advisable to white-list the table name since you cannot prevent it being a SQL injection hole.
I am extending last paragraph from @MarcGravell answer.
It only works with one variable
I guess when you say that, you mean it only works with one value of TableName
vairable. Your code works when TableName
is pointing to your PersonTable
or similar and fails when it points to other table with different name and columns.
You are using TableName
as a variable. That means same query will execute with different table name. In that case, using strongly typed overload of Query
(Query<Person>(...)
) MAY not work; I am not sure because the question is missing your possible table definitions and definition of Person
class.
Querying anonymous will be the better solution in this case. Please refer sample here.
So, following may be sample code:
var result = connection.Query(
"select * from " + TableName + " ...",
new { searchData }).AsList();
return result;
Apart from that, avoid injecting table name in query. Instead, write separate code for each table or domain model. Please consider Repository pattern if it suits your needs.
Also, please consider implementing the improvements and corrections in other answer and comments.