c# to call MySQL Update Stored Proc error

jonmy

Hello, I'm trying to call the following MySQL SP:

DROP PROCEDURE IF EXISTS tgc.UpdateCustomerBalance;
CREATE PROCEDURE tgc.`UpdateCustomerBalance`(customerId varchar(50), cost Decimal(8,2))
BEGIN
UPDATE Customers SET Balance = Balance - cost WHERE CustomerId = customerId;
END;

with the following C# code:

try
{
using (var context = new Db_Entities())
{
object[] parameters =
{
new MySql.Data.MySqlClient.MySqlParameter("@customerId", customerId),
new MySql.Data.MySqlClient.MySqlParameter("@cost", cost)
};

            await context.Database.ExecuteSqlCommandAsync("UpdateCustomerBalance @customerId,@cost", parameters);
            }
        }
        catch (Exception e)
        {
            throw;
        }

But I receive the error:

"Message": "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UpdateCustomerBalance 'xyz',0.03' at line 1",

Can anyone help shed some light on this? I've tried all sort and am now seeing tunnel vision. Incidentally, this works:

await context.Database.ExecuteSqlCommandAsync("UPDATE Customers SET Balance = Balance - " + cost + " WHERE CustomerId = '" + customerId + "'");

All help appreciated.

Richard Deeming

The MySQL syntax is subtly different to the MSSQL syntax. Try using CALL[^]:

await context.Database.ExecuteSqlCommandAsync("CALL UpdateCustomerBalance(@customerId,@cost);", parameters);

jonmy wrote:

Incidentally, this works:

await context.Database.ExecuteSqlCommandAsync("UPDATE Customers SET Balance = Balance - " + cost + " WHERE CustomerId = '" + customerId + "'");

It might work, but it's vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query. In Entity Framework, you can simply use:

await context.Database.ExecuteSqlCommandAsync("UPDATE Customers SET Balance = Balance - {0} WHERE CustomerId = {1}", cost, customerId);

EF will replace the placeholders ({0} and {1}) with auto-generated parameter names, and add the parameter values (cost and customerId) as parameters.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^] How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^] Query Parameterization Cheat Sheet | OWASP[^]

jonmy

Richard, Worked a treat, thank you. I've trawled the sites looking for a solution but didn't come across the CALL keyword - so very frustrating. On a side note, from a performance perspective, is there a better way of this being implemented? The solution could take a large throughput and I'm keen to understand any improvements. Again, thanks for the tip. JMY.