I am writing a client-server app and using a SQL Server database. There is a moment where I need to insert info about new guardian, but something goes wrong.

Here is this piece of code:

sqlQuery = $"INSERT INTO [dbo].[Guardians] ([Login], [Password], [AdminAccess]) VALUES (N'{newLogin}', N'{newPassword}', {newAdminAccess})";

cmd = new SqlCommand(sqlQuery, sqlConnection);

if (cmd.ExecuteNonQuery() == 1)
{
    listener.Send(Encoding.UTF8.GetBytes("Success"));
}
else
{
    listener.Send(Encoding.UTF8.GetBytes("Failure"));
}

And here is my DB structure:

DB image.

I can`t understand where is the mistake for two days, or something like that. Help me please.

CodePudding user response：

While this doesn't seem to address the question, it does fix two MAJOR security issues and one minor denial of service issue, and in my experience using parameterized queries to fix the SQL injection issue also tends to help with these syntax errors as well. In other words, do it right, and you wouldn't have run into this issue in the first place.

sqlQuery = "INSERT INTO [dbo].[Guardians] ([Login], [Password], [AdminAccess]) VALUES (@Login, @Hash, @Admin)";

//It really is better to create a new connection for most queries. Don't try to re-use it throughout an app. Only re-use the connection string.
using var conn = new SqlConnection("connection string here");
using var cmd = new SqlCommand(sqlQuery, conn);

// Use actual parameter types and lengths here
cmd.Parameters.Add("@Login", SqlDbType.VarChar, 50).Value = newLogin;
cmd.Parameters.Add("@Hash", SqlDbType.Char, 60).Value = BCrypt.HashPassword(newPassword); // a bcrypt hash is ALWAYS EXACTLY 60 chars
cmd.Parameters.Add("@Admin", SqlDbType.Boolean).Value = newAdminAccess;

conn.Open();
if (cmd.ExecuteNonQuery() == 1)
{
    listener.Send(Encoding.UTF8.GetBytes("Success"));
}
else
{
    listener.Send(Encoding.UTF8.GetBytes("Shit"));
}