how to use c# variables in sql commands

Question

This is what I want to use;

string insertQuery = @"insert into userinfo (UserName, FirstName, LastName, 
                      E-mail, Password,  Country) 
                      values ( '" + uname + "', '" + fname + 
                      "','" + lname + "', '" + email + "')";

where every one of the variables in between the + are string variables with values in them. but when I run this command, I get some incorrect syntax error.

This is the new error i get;

errorSystem.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Registration.RegistrationPage.Button1_Click1(Object sender, EventArgs e) in c:\Users\kristhnen.jagambrum\Documents\Visual Studio 2012\Projects\Registration\Registration\RegistrationPage.aspx.cs:line 50 ClientConnectionId:6d959e49-5b62-43be-b202-76f7eb1fbd2c

Answer 1

It's very good that you asked - the code that you show is a perfect illustration of two security issues in a single line:

• The table structure is an illustration of what lets hackers steal user passwords.
• The C# code is what makes SQL injection attacks possible.

Fixing the first problem is hard: you need to learn about password hashing and how to store user info in DB. Here is a Q&A that will help: Best way to store passwords in DB

The second problem is easier - all you need is replacing the injected values with parameter names, and then adding the values for each parameter name:

... // Create SQL command, then set its text
command.CommandTest = @"INSERT INTO userinfo (
    UserName, FirstName, LastName, E-mail, Password_hash, Password_salt, Country
) VALUES ( @uname, @fname, @lname, @email, @pwd_hash, @pwd_salt, @country)";
// Bind the parameters
command.Parameters.Add(new SqlParameter("@uname", uname));
command.Parameters.Add(new SqlParameter("@fname", fname));
... // ...and so on
command.ExecuteNonQuery();

Answer 2

The answer is you don't, because it is a bad idea. You should be using SQlCommand instead

Check This out,and there are plenty of examples of how to use it. Appending variables in the way you doing is considered to be the mistake number 3 in the list of 25 most dangerous programming mistakes.

Answer 3

Try something like this, and not use direct parameter in sql command

public const string InsertStmtUsersTable = "insert into userinfo (UserName, FirstName, LastName, 
                      [E-mail], Password,  Country) values (@UserName, @FirstName, @LastName, 
                      @[E-mail], @Password,  @Country) "
    using(SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();

        using (SqlCommand command = new SqlCommand(InsertStmtUsersTable, conn))
        {
           command.CommandType = CommandType.Text;

           command.Parameters.Add(new SqlParameter("username", userNameString));
           command.Parameters.Add(new SqlParameter("FirstName", FirstNameString));
           // Rest of your Parameters here

           command.ExecuteNonQuery();
        }
    }