Query doesn't execute due error within c# code due to internal parameter

Question

I think I'm missing something obvious here. The query executes in SQL server no problem. I've googled around and I found out you can use AddWithValue on the SQL command for the parameter @UserID but the issue is I need to search the database to get it. Below is the error the application is generating :

02:59:10,578 fail: Microsoft.AspNetCore.Server.Kestrel[13] Connection id "0HMJJMIDP0K9M", Request id "0HMJJMIDP0K9M:00000002": An unhandled exception was thrown by the application. System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '@UserID'. Incorrect syntax near '.'. Incorrect syntax near 'VJ'. ```

Here is my code

        {
            var connString = decryptionService.DecryptFromBase64String(options.Value.ConnectionString); 

            using var conn = new SqlConnection(connString);
            await conn.OpenAsync();

            using var command = conn.CreateCommand();
            command.CommandText = GetSql(request.ScriptName, request.RecoveryCode);
            command.CommandType = CommandType.Text;
            await command.ExecuteNonQueryAsync();
            
            return Ok("Done");
        }

You're using string interpolation to stuff in `recoveryCode`. Don't do that, use a proper parameter. `@UserID` is not the issue here. — Jeroen Mostert, Aug 02 '22 at 06:57
Does this answer your question? [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) — Charlieface, Aug 02 '22 at 11:14
Side note: `command.CommandType = CommandType.Text;` is the default anyway, and you can initliaze the command like this `using var command = new SqlCommand(GetSql(request.ScriptName, request.RecoveryCode), conn);` — Charlieface, Aug 02 '22 at 11:15
@MakePeaceGreatAgain don't think it's [mandatory](https://stackoverflow.com/questions/710683/when-should-i-use-semicolons-in-sql-server) but still good practice. — Pimenta, Aug 04 '22 at 14:35

score 0 · Answer 1 · answered Aug 04 '22 at 14:33

I have to agree with all the above comments:

use parameters instead of string interpolation
@UserId is not the issue

Without trying out, I see that recoveryCode is a string but no '' have been set in it's comparison - also maybe good to use LIKE for string values.

$"WHERE RecoveryCode = {recoveryCode} AND Id = @UserID ",

Should be

$"WHERE RecoveryCode = '{recoveryCode}' AND Id = @UserID ",

Query doesn't execute due error within c# code due to internal parameter

1 Answers1