C# MVC Web application SQL not working properly

Question

Currently I am facing issue in my web application.

• Background > This web application is working well for 4 to 5 years

Recently, I receive the error message return as below. (Noted: My Code never changes since 2 to 3 years ago)

Unknown column ''' in 'where clause'

Below is my query. And the query work well when running in HeidiSQL

internal static readonly string GetTableSQL = @" SELECT u.* FROM tableA u WHERE REPLACE(u.COLUMN_A,'\'','') IN (@CompanyCodes) "

** @CompanyCodes will pass as parameters

I use REPLACE because COLUMN_A value has a single quote. That's why I want to replace it.

On 17-Feb-2023, my application start throw this error. One strange thing is, when ever the error is throw, I restart my IIS and App pool, then the application work well for may be 30 min, after that the error throw again until I restart again my IIS service.

I do notice that on 16-Feb-2023, there is one window update on .NET Framework 3.5, 4.6.2, 4.7, 4.7.1, 4.7.2, 4.8 for Windows Embedded 8.1 and Windows Server 2012 R2 (KB5022733). But I am not sure does this error related to this window update.

http://support.microsoft.com/kb/5022733

I do try different method of code such as

Web application details:

• DB - Mariadb (MySQL)
• Web C# MVC
• Hosting Server: Windows server 2012 R2
• IIS 8

Does anyone know how to troubleshoot or what is the root cause ? Please help. Much appreciate!

Answer 1

The error Unknown column ''' in 'where clause' is happening because you're using string concatenation to build your SQL query.

Instead of doing this, you should use DbCommand parameters to pass the values. With a library like Dapper, this would look something like:

using var connection = GetYourConnection();
var companyCodes = new[] { "CODE1", "CODE2", ... };
var results = connection.Query<CompanyObject>(
    "SELECT u.* FROM tableA u WHERE u.COLUMN_A IN @companyCodes"
    new { companyCodes });