Incorrect Syntax near 'username' - Error c#

Question

I tried everything and searched about the related topic and nothing works..

Here's my code

            {
                byte[] images = null;
                FileStream fs = new FileStream(imgLocation, FileMode.Open, FileAccess.Read);
                BinaryReader br = new BinaryReader(fs);
                images = br.ReadBytes((int)fs.Length);

                SqlConnection con2 = new SqlConnection(@"Data Source=USER-PC; Initial Catalog=ProfRegistration; Integrated Security=True;");
                con2.Open();
                String str2 = "INSERT INTO ProfInfo(UserID,FirstName,MiddleName,LastName,Department,Username,Password,Image)  VALUES('" + txtID.Text + "' , '" + txtFirstName.Text + "' , '" + txtMiddleName.Text + "' , '" + txtLastName.Text + "', '" + txtDep.Text + "' '" + txtUsername.Text + "', '" + txtPassword.Text + "', @Image)";
                SqlCommand cmd2 = new SqlCommand(str2, con2);
                cmd2.Parameters.Add(new SqlParameter("@UserID", txtID.Text));
                cmd2.Parameters.Add(new SqlParameter("@FirstName", txtFirstName.Text));
                cmd2.Parameters.Add(new SqlParameter("@MiddleName", txtMiddleName.Text));
                cmd2.Parameters.Add(new SqlParameter("@LastName", txtLastName.Text));
                cmd2.Parameters.Add(new SqlParameter("@Department", txtDep.Text));
                cmd2.Parameters.Add(new SqlParameter("@Username", txtUsername.Text));
                cmd2.Parameters.Add(new SqlParameter("@Password", txtPassword.Text));
                cmd2.Parameters.Add(new SqlParameter("@Image", images));
                cmd2.ExecuteNonQuery();
                MessageBox.Show("User has been added!");
                Login frmLogin = new Login();
                frmLogin.Show();
            }

I already used cmd.Parameters.Add(...... instead of cmd.Parameters.AddWithValue(.... since it gives more errors and exception..

Why are you concatenating SQL if you are already using SQL Parameters? You are wide open for SQL injection attacks... If you used SQL parameters the proper way you would not have an error. — maccettura, Mar 05 '18 at 17:33
Password is a reserved word in most SQL dialects. Also never store passwords as plain text - hash and salt them. Any form of Add/AddWithValue will cause errors since your SQL doesnt have any parameters (but should). And you dont need a binary reader to get the image - `File.ReadAllBytes()` will do swell. — Ňɏssa Pøngjǣrdenlarp, Mar 05 '18 at 17:33

maccettura · Accepted Answer · 2018-03-05T17:42:27.143

The reason for your error is because your syntax is wrong, however your syntax being wrong is only part of the problem.

Your code is wide open for SQL injection attacks.

The proper way of using parameterized queries is to include the parameters in your query. This will make it much easier to debug since you wont have a thousand concatenated strings:

string query = "INSERT INTO ProfInfo(UserID,FirstName,MiddleName,LastName,Department,Username,Password,Image)  VALUES(@UserID, @FirstName, @MiddleName, @LastName, @Department, @Username, @Password, @Image)";

SqlCommand cmd2 = new SqlCommand(query , con2);
cmd2.Parameters.Add(new SqlParameter("@UserID", txtID.Text));            
cmd2.Parameters.Add(new SqlParameter("@FirstName", txtFirstName.Text));
cmd2.Parameters.Add(new SqlParameter("@MiddleName", txtMiddleName.Text));
cmd2.Parameters.Add(new SqlParameter("@LastName", txtLastName.Text));
cmd2.Parameters.Add(new SqlParameter("@Department", txtDep.Text));
cmd2.Parameters.Add(new SqlParameter("@Username", txtUsername.Text));
cmd2.Parameters.Add(new SqlParameter("@Password", txtPassword.Text));
cmd2.Parameters.Add(new SqlParameter("@Image", images));

This however is not all of your problems.

Besides the massive SQL injection vulnerability you are also storing your passwords as plain text. This is a horrible idea. You need to salt & hash your passwords before storing.

I strongly suggest dedicating some time researching some basic security topics like SQL injection and password salting & hashing. There are loads of free resources available.

LMFAO.. why it doesn't write in SQL Database?? in just say "User has been added".. then i look back to Table it doesn't appear (i've already refreshed it). — Don Gil, Mar 05 '18 at 18:26

score 0 · Answer 2 · answered Mar 05 '18 at 17:43

The basic problem in your code is that you missed comma , in sql near username

+ "' '" + txtUsername.Text + "', '

Use

+ "', '" + txtUsername.Text + "', '

Rest your code need fixing as mentioned in other answer. Like SQL Injection

Also you are using parameters in sqlcommand object but they are not in the sql query. Add them in query

Incorrect Syntax near 'username' - Error c#

2 Answers2