CODEWITHSUNDEEP
c#
steffy
steffy

Reputation: 17

System.Data.SqlClient.SqlException on dataadapter.Fill(databtable);

I have this program which I am working into. Then when I started to debug it, it seems perfectly running smooth. But then it just stops and then a note popped up

System.Data.SqlClient.SqlException Incorrect syntax near '`'.'

Here is my code.

public void searchData(string valueToSearch)
{
     string query = "SELECT * FROM users WHERE CONCAT(`lastname`, `middle`, `firstname`, `username`) like '%" + valueToSearch + "%'";
     SqlCommand command = new SqlCommand(query, connection);
     SqlDataAdapter sda = new SqlDataAdapter(command);
     DataTable dt = new DataTable();
     sda.Fill(dt);
     dataGridView1.DataSource = dt;
}

and the message popped up on sda.Fill(dt); and I don't really know what part is the problem since it doesn't show up on the error list.

Please help me.

Upvotes: 0

Views: 573

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1062484

There are two problems in your SQL; the immediate one is that you're using the wrong variant of escaping. SQL is "more what you call guidelines than actual rules", and different vendors use different rules. You're using backtick escaping of the form:

`identifier`

You also mention SqlClient in the question, which tells us that you're using SQL Server. SQL Server uses TSQL syntax, where [identifier] is the correct escape syntax - and it is optional, so unless your column/table names are reserved keywords you can just use the name directly:

 WHERE CONCAT(lastname, middle, firstname, username)

The second and IMO much more serious problem is: SQL injection. You should never ever concatenate user input into a query. That's how a huge number of data breaches and outages happen - it is a huge security hole, and the sooner you learn not to do that: the better. Instead, use parameters:

 string query = "... like @searchVal";
 SqlCommand command = new SqlCommand(query, connection);
 command.Parameters.AddWithValue("@searchVal", "%" + valueToSearch + "%");
 // ...

This completely protects you from SQL injection (unless you've done something silly inside the SQL, such as EXEC-ing it), and (in the case of numbers / dates / etc) solves a wide range of "culture" issues (such as whether "123,456" is "one hundred and twenty three thousand four hundred and fifty six", or whether it is "one hundred and twenty three point four five six").

Upvotes: 3

Praneet Nadkar
Praneet Nadkar

Reputation: 813

The params in the concat function are surrounded by quotes, which doesnt make sense. Those have to be the column names.

Change your query object to :

var query = "SELECT * FROM users WHERE CONCAT(lastname, middle, firstname, username) like '%" + valueToSearch + "%'";

Upvotes: 4

Related Questions