incorrect syntax when I select, the error near the first name

Question

I have a little problem with an error. but I have this command in another form and do not give me the error.

This is the code:

string select = "select CONCAT(nume,' ',prenume) from echipa where email=@EMAIL";

            cmd.Connection = con;


            if (bunifuCheckbox1.Checked == true)
            {
                con.Open();
                cmd.CommandText = "Insert into clienti_fizici(nume,prenume,email,telefon,adresa,data_nasterii,data_ora,CNP,sex,judetprovenienta,temperamentclient,provenientaclient,descriere,numeagent)values('"
+ bunifuMaterialTextbox1.Text + "','" + bunifuMaterialTextbox2.Text + "','" + bunifuMaterialTextbox4.Text + "','" + bunifuMaterialTextbox8.Text + "','" + bunifuMaterialTextbox3.Text + "','" + DateTime.Now.ToString("yyyy-MM-dd HH: mm:ss") + "','" + bunifuDatepicker1.Value.Date + "','" + bunifuMaterialTextbox11.Text + "','" + gender + "','" + bunifuMaterialTextbox12.Text + "','" + bunifuDropdown1.selectedValue + "','" + bunifuDropdown2.selectedValue
+ "','" + richTextBox1.Text + "','" + select + "')";
                cmd.Parameters.AddWithValue("@EMAIL", loginform.Email);
                MessageBox.Show("Datele au fost introduse in baza de date !");
                cmd.ExecuteNonQuery();
                con.Close();
            }

and the error would be from that select.

What exactly are you trying to do? Insert the value of the select? Insert the SQL statement as a string? If the first, it won’t work this way. If the second, parametrize the rest of the query also and it will work. — Sami Kuhmonen, Dec 11 '17 at 11:30
I have just answered a question like this, check the example: https://stackoverflow.com/questions/47750505/incorrect-syntax-near-s-unclosed-quotation-marks-after-the-character-string/ — Rogério Carvalho Batista, Dec 11 '17 at 11:35
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Dec 11 '17 at 11:39
@mjwills exception states System.Data.SqlClient - meaning SQL Server. Also, using parameters is just a part of the problem. It's not a duplicate of the question you or Rogério linked to. The syntax error is because the use of a `select` inside the `values` clause. If anything, it's a duplicate of [Subqueries are not allowed after VALUES?](https://stackoverflow.com/questions/47324426/subqueries-are-not-allowed-after-values). However, I didn't mark it as duplicate since this question has a bigger problem (the SQL injection hazard, of course). — Zohar Peled, Dec 11 '17 at 11:46

score 0 · Accepted Answer · answered Dec 11 '17 at 11:42

First, you must never concatenate strings with user input to create SQL Statement. Instead, always parameterize your SQL statements. Otherwise you are risking SQL injection attacks.

Second, you can't use select inside the values clause.
What you can do add parameters or hard coded values to your select statement.

Third, SqlConnection and SqlCommand both implement the IDisposable interface and should be used as a local variable inside a using block.

A better code would look like this:

if (bunifuCheckbox1.Checked == true)
{

    string sql = "Insert into clienti_fizici(nume, prenume, email, telefon, adresa, data_nasterii, data_ora, CNP, sex, judetprovenienta, temperamentclient, provenientaclient, descriere, numeagent) " + 
                 "SELECT @nume, @prenume, @email, @telefon, @adresa, @data_nasterii, @data_ora, @CNP, @sex, @judetprovenienta, @temperamentclient, @provenientaclient, @descriere, CONCAT(nume,' ',prenume) " + 
                 "FROM echipa where email = @EMAIL";

    // Note: SqlConnection should be opened for the shortest time possible - the using statement close and dispose it when done.
    using(var con = new SqlConnection(connectionString))
    {
        // SqlCommand is also an IDisposable and should be disposed when done.
        using(var cmd = new SqlCommand(sql, con)
        {
        cmd.Parameters.Add("@nume", SqlDbType.NVarChar).Value = bunifuMaterialTextbox1.Text;
        cmd.Parameters.Add("@prenume", SqlDbType.NVarChar).Value = bunifuMaterialTextbox2.Text;
        //... Add the rest of the parameters here...
        cmd.Parameters.Add("@EMAIL", SqlDbType.NVarChar).Value = loginform.Email;
        // Why is this here? MessageBox.Show("Datele au fost introduse in baza de date !");
        con.Open();
        cmd.ExecuteNonQuery();
        }
    }
}

Thank you, i put the parameters and added the selection and now it works. — Iulian Glăvan, Dec 11 '17 at 12:00

incorrect syntax when I select, the error near the first name

1 Answers1