Syntax for injecting SQL Server SQL statements into c# code

Question

I have this c# code to retrieve data from a SQL Server database with ADO.NET:

//Define ADO.NET objects
    string selectSQL;
    selectSQL = "SELECT * FROM slip ";
    selectSQL += "WHERE slip_id='" + txtSlipID_srch + "'";

I have read the syntax in a book and have been given the syntax at college, but I haven't been given an explanation for it, other than "it's the syntax". I'm talking about the following syntax:

'" + txtSlipID_srch + "'

ie the field name txtSlipID_srch is bound by a single quote, double quotes AND a plus sign.

Can someone decipher this for me please? I'd appreciate it.

Answer 1

Whoever has taught you this syntax has really taught you a wrong thing.
This syntax, as you have guessed, joins the content of the string txtSlipID_srch to your SQL text forming a complete instruction from a fixed text and a variable part. The whole thing is then passed to the database engine for the execution.

But allowing a user to type something in a textbox and then using that input to build your SQL queries is really a wrong thing

The user can type anything and that anything could also be a well forged string that changes your intent and destroys your database or gets information that you don't want him to see (passwords or credit card numbers). It is called Sql Injection and there are thousands of articles on how to implement this hacking technique. I don't want to repeat anything, you could simply look at this well known comics+question+answers and read the explanations below

Apart from this. There is the problem of the correct parsing of strings. The presence of a single quote inside in your string variable will render your query invalid because single quotes are used to delimit string values passed to the database. The same happens with decimals and dates that should be transformed in strings with a conversion procedure. Also here you should create the correct text for the database (Does it like a comma or point for decimals?, the format for dates is 'dd/MM/yyyy' or 'MM/dd/yyyy' or what? and so on)

The only valid approach is to use a parameterized query where you write your command text in this way

selectSQL = "SELECT * FROM slip WHERE slip_id=@id";

Now there is no more the concatenation of the two strings and no single quotes around string values to 'escape' them, but just a parameter placeholder named @id.

The ADO.NET library will provide the appropriate classes to handle that parameter, passing the value to the database engine where it will be treated correctly.

SqlCommand cmd = new SqlCommand(selectSQL, connection);
cmd.Parameters.Add("@id", SqlDbType.NVarChar).Value = txtSlipID_srch;
SqlDataReader reader = cmd.ExecuteReader();
...... 

Answer 2

Because you want the resulting SQL to look like this:

SELECT * FROM slip WHERE slip_id='somevalue'

It needs the single quotes in the resulting SQL statement.

The double quotes are to define the string in the C# code. The single quotes are to define the string in the SQL statement.

Answer 3

The reason for using single quotes is because SQL expects string values to be enclosed in single quotes. (as explained in other answer)

But do not use string concatenation to create SQL queries. Your input could be coming from a user and any malicious user can exploit it. Read about SQL Injection.

You can do that in a better way:

Use Parameters

using (SqlConnection conn = new SqlConnection("connection string"))
using (SqlCommand cmd = new SqlCommand("SELECT * FROM slip WHERE slip_id=@slipID", conn))
{
    cmd.Parameters.AddWithValue("@slipID", txtSlipID_srch);//Even use .Add and specify data type
    //open connection 
    //execute command
}

using statement to dispose off resources after the execution of command.

(Also if your book doesn't mention using parameters later, then you are better off without that book, read from somewhere else)

Answer 4

in this case the + is a concatenation operator. In the example txtSlipID_srch is a variable that holds a value and you are concatenating that value into another string (ie, your SQL statement). Just like if I wanted to say Hello Maarten how are you today? I would write string greeting = "Hello " + yourNameVariable + " how are you today?";. So when Maarten is stored in the yourNameVariable it takes that information and concatenates it with the other two strings. Notice how I also had to include the spaces to ensure that the string was displayed properly? But as everyone has pointed out, this should never be used when building sql statements (which are just strings themselves) because this gives the user the ability to pass in a string that can let them execute more damaging statements against the database.