C# invalid column name problem but is different from others-CodePudding

Hello i have a table called Card_tbl with a cell CardID. The problem is that when i insert only numbers like 12345 they are uploaded in the database, but when i use mix letters like Q1234R it will say INVALID COLUMN NAME Q1234R I tried many things to fix it but no success. THE CELL IS ON VARCHAR(50) this is my code

Con.Open();
            SqlCommand cmd = new SqlCommand("insert into Card_tbl values("  txtCardNumber.Text ")", Con);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Admin Successfully Added!");
            Con.Close();
            populate();
            //updateroomstate();
            Clean();

the error is shown here cmd.ExecuteNonQuery();

Thank you in advance.

CodePudding user response：

Try this instead

Insert into Card_tbl(CardID) values('" txtCardNumber.Text "')"

https://www.w3schools.com/sql/sql_insert.asp

CodePudding user response：

Solution

The correct solution is to use prepared statements. You've said the column is a varchar(50) so:

using (SqlConnection conn = new SqlConnection("yourconnectionstring"))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO Card_tbl VALUES (@cardNumber)", conn))
    {
        // we specify type VarChar and size 50, because your column is a varchar(50)
        cmd.Parameters.Add("@cardNumber", SqlDbType.VarChar, 50).Value = txtCardNumber.Text;
        cmd.ExecuteNonQuery();
    }
}

I would also recommend that you specify the column name for the insert:

INSERT INTO Card_tbl (CardId) VALUES (@cardNumber) -- I've assumed that the column is called CardId

Why do we need parameterized queries / prepared statements?

The reason we need prepared statements is because the user could enter something malicious in the query, forcing your server to execute queries that you didn't intend.

If you have a query like this, SQL Server will do the insert and then drop the table:

INSERT INTO MyTable VALUES ('hello'); DROP TABLE MyTable; -- comment

This is a multi-statement query, meaning that the INSERT will be done first, and then the DROP TABLE will be executed. The -- denotes a comment, and everything after that in the query is discarded.

Now, if you build a query like "INSERT INTO MyTable VALUES ('" someUserInput "')", how could a malicious user make this query more like the previous one? Simple: enter SQL into their input.

Suppose someUserInput is '); DROP TABLE MyTable; -- comment then your query will become:

INSERT INTO MyTable VALUES(''); DROP TABLE MyTable; -- comment')

Like this, a malicious user can perform an extremely destructive operation on your database, bypass login pages, expose secret information, etc. This is a huge risk for your application.

Prepared statements work by separating the query text from the parameter values. Because they're not part of the query, they can't be interpreted as SQL. It also means that binary data types can be sent to the server as binary rather than as text. Example: DateTime.