• (2 Pages)
  • +
  • 1
  • 2

Parameterizing your SQL Queries: The RIGHT way to query a database.

#1 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4312
  • View blog
  • Posts: 7,468
  • Joined: 08-June 10

Post icon  Posted 24 February 2012 - 03:20 PM

*
POPULAR

I've been noticing a trend for quite some time. Lots of people seem to write applications that use databases, but they have very little idea of how databases work. There are lots of tutorials out there with bad advice. For instance, if you see someone suggesting that you use string concatenation to build your queries, that's usually bad advice. Parameterized queries are a very good thing.

This is not intended to be a general "how to use a database" tutorial. This is specifically a tutorial on how to use SQL paramters in your program. It assumes you have a basic understanding of how to perform simple queries against your database, and are familiar with concepts such as queries and connection strings.

Parameters are nice for a number of reasons. Tell me, which looks better to you?

Option 1)
string sql = "select * from table where fname = '" + firstName + "' and lname = '" + lastName + "'";


Option 2)
string sql = "select * from table where fname = @fname and lname = @lname";


Of course, Option 2 looks better, especially if you understand it. Parameters are like SQL's version of variables. They always start with an @ symbol. They represent a value. Notice, for example, that I didn't have to quote my parameters, even though they represent strings. That's just like in C#, where you don't have to surround your string variables with quotes when you use them, because they represent a string rather than hardcoding a string constant.

So, not only is it more readable, and you never have to worry about if you have the right quotation marks, but it's also safer. Most people never think about their SQL being safe or unsafe, but it's a big problem. Read up on SQL Injection Attacks. SQL Server will take whatever you give it and execute it. So, it's entirely possible for someone to slip in some malicious SQL into your field, if you're not careful. But parameters take care of all that for you. It escapes SQL syntax, so that all parameter content is treated as a value, never a command.

One final important reason to use parameters: sometimes, it's almost impossible otherwise. For instance, adding data to a varbinary field. How do you insert binary into a query? Not easily. But you can simply use a byte[] as the value of a parameter, and it works beautifully.

Those are the reasons you should use SQL Parameters. But how do you do it? Well, you have to have a DbCommand object. Specifically for SQL Server, that'll be the SqlCommand class. For other database types, you might use OleDbCommand, ODBCCommand, OracleCommand, etc...but for this example we'll use SQL Server.

SqlCommand has a collection of parameters, appropriately named Parameters. This has a very handy method on it named "AddWithValue". It takes a string and an object. First, the name of the parameter, and next the value to be set to that parameter. The best way to understand this is to see it in action.

Open the spoiler tag if you'd like to set your database up the same way as mine.
Spoiler


note: for all the following examples, I have a constant defined.
//use http://www.connectionstrings.com to find a connection string that works for you
public const string ConnectionString = @"Data Source=.\Sqlexpress;Initial Catalog=Example;Integrated Security=SSPI;";



The following is a method that performs a SELECT. Note line 5: it's where the parameter is given a value.

private static void Select() {
    string cmdStr = "SELECT FirstName, LastName, Telephone FROM Person WHERE FirstName = @FirstName";
    using (SqlConnection connection = new SqlConnection(ConnectionString))
    using (SqlCommand command = new SqlCommand(cmdStr, connection)) {
        command.Parameters.AddWithValue("@FirstName", "John");
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read()) {
            string output = "First Name: {0} \t Last Name: {1} \t Phone: {2}";
            Console.WriteLine(output, reader["FirstName"], reader["LastName"], reader["Telephone"]);
        }
    }
}


This also works for other SQL actions, like INSERT for example. This time, the magic's on lines 5 - 7.

private static void Insert() {
    string cmdStr = "INSERT INTO Person (FirstName ,LastName ,Telephone) VALUES (@FirstName, @LastName, @Telephone)";
    using (SqlConnection connection = new SqlConnection(ConnectionString))
    using (SqlCommand command = new SqlCommand(cmdStr, connection)) {
        command.Parameters.AddWithValue("@FirstName", "Bob");
        command.Parameters.AddWithValue("@LastName", "Johnson");
        command.Parameters.AddWithValue("@Telephone", "456-789-1230");
        connection.Open();
        int affectedRows = command.ExecuteNonQuery();
        Console.WriteLine("Affected Rows: {0}", affectedRows);
    }
}


You can also use parameters for Stored Procedure Queries. The difference is, you don't include the parameters as part of the command string, but you still add them to the command object. Here's an example of both an INSERT and a SELECT using Stored Procedures.

private static void SelectWithSP() {
    string cmdStr = "pSelectPersonByFirstName";
    using (SqlConnection connection = new SqlConnection(ConnectionString))
    using (SqlCommand command = new SqlCommand(cmdStr, connection)) {
        command.Parameters.AddWithValue("@FirstName", "John");
        command.CommandType = CommandType.StoredProcedure;
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read()) {
            string output = "First Name: {0} \t Last Name: {1} \t Phone: {2}";
            Console.WriteLine(output, reader["FirstName"], reader["LastName"], reader["Telephone"]);
        }
    }
}

private static void InsertWithSP() {
    string cmdStr = "pInsertPerson";
    using (SqlConnection connection = new SqlConnection(ConnectionString))
    using (SqlCommand command = new SqlCommand(cmdStr, connection)) {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@FirstName", "Bob");
        command.Parameters.AddWithValue("@LastName", "Johnson");
        command.Parameters.AddWithValue("@Telephone", "456-789-1230");
        connection.Open();
        command.ExecuteNonQuery();
    }
}


Now that you see how it's done, there's almost never a reason not to use parameters. They only add a tiny bit of extra code, but they grant you readability, maintainability, and security. There's really no downside. So, go forth and use parameters for the rest of your days.

Side note: ORMs often parameterize queries for you. I know from experience that LINQ to SQL and Entity Framework use parameters for all the queries they perform. I can't speak definitively for any of the rest, but I'd be highly surprised if they didn't.

Is This A Good Question/Topic? 26
  • +

Replies To: Parameterizing your SQL Queries: The RIGHT way to query a database.

#2 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 115
  • View blog
  • Posts: 1,332
  • Joined: 14-January 10

Posted 26 August 2012 - 03:37 PM

Excellent tutorial! Using parameters was recommended to me by someone on another forum and now I know what they are talking about.
Was This Post Helpful? 0
  • +
  • -

#3 CharlieMay  Icon User is online

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,920
  • Joined: 25-September 09

Posted 19 September 2012 - 11:46 AM

Thanks Curtis,
This can't be expressed enough.

A lot of problems people have with statements can be resolved by simply using parameters.

I've had people with questions about a Syntax Error in their statement and couldn't for the life of them find it. Of course, it's hard to see the error when all you're asked is what the problem is in a statement like this:

"SELECT * FROM myTable WHERE FName = '" & txtFN.Text & "' AND LName = '" & txtLN.Text & "'"

Now that's a pretty basic query, and of course there aren't any syntax errors in it. That is, unless your textboxes contain something like "Bill" and "O'Neal".

In that case, you've just created your own Syntax Error. Why? Well, because you just made your statement read:
SELECT * FROM myTable WHERE FName = 'Bill' And LName = 'O'Neal'
Notice the extra apostrophe
And this is the reason SQL injection works. The string is looked over and parsed and any commands that follow syntax can be executed regardless of what you want to happen. Parameters remove this ability and keep the statement safe.

The biggest complaint I hear from users is that it's more code and takes longer. Trust me, As was pointed out in this Tutorial, the ease of reading and following the statement with parameters, by far offsets the complexity of trying to generate a concatenated statement correctly the first time.

If you would like to see a mild example of what can be done with SQL Injection, there is a link in my sig. I say mild because this example doesn't actually cause damage to your database, which, is completely possible with injection.
Was This Post Helpful? 0
  • +
  • -

#4 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Posted 24 October 2012 - 08:21 AM

Curtis, I was pointed in your direction from someone that responded to one of my posts. I'm having an issue where I need to use LIKE in my select statement. After I implemented your above select code, my select statement is now returning nothing when it has been returning at least 30 responses for the same db query before I tried the above code. Any suggestions?

    '**************************************************************
    '
    '                       Staff Functions
    '
    '**************************************************************
    Public Function Staff(param As String) As String Implements CentralService.Staff
        SQLStr = "SELECT Name_First, Name_Last, email_MSU, PhotoFileName FROM tbl_people WHERE Name_First LIKE @partial OR Name_Last LIKE @partial"
        Dim jss As JavascriptSerializer
        Dim Employees As New List(Of Employee)

        Dim FName As String = ""
        Dim LName As String = ""
        Dim Email As String = ""
        Dim PhotoURL As String = ""

        jss = New JavascriptSerializer()

        Using SQLConn As New SqlConnection(ConnString)
            Using SQLCmd As New SqlCommand(SQLStr, SQLConn)
                SQLCmd.Parameters.AddWithValue("@partial", "'%" & param & "%'")
                SQLConn.Open()
                Dim SQLdr As SqlDataReader = SQLCmd.ExecuteReader()

                Try
                    While SQLdr.Read()
                        If IsDBNull(SQLdr.Item("Name_First")) Then
                            FName = ""
                        Else
                            FName = SQLdr.Item("Name_First")
                        End If
                        If IsDBNull(SQLdr.Item("Name_Last")) Then
                            LName = ""
                        Else
                            LName = SQLdr.Item("Name_Last")
                        End If
                        If IsDBNull(SQLdr.Item("email_MSU")) Then
                            Email = ""
                        Else
                            Email = SQLdr.Item("email_MSU")
                        End If
                        If IsDBNull(SQLdr.Item("PhotoFileName")) Then
                            PhotoURL = ""
                        Else
                            PhotoURL = SQLdr.Item("PhotoFileName")
                        End If
                        Employees.Add(New Employee() With {.firstName = FName, .lastName = LName, .email = Email, .photo = PhotoURL})
                    End While
                Catch ex As Exception
                    Return ex.ToString
                End Try
            End Using
        End Using

        Return jss.Serialize(Employees)

    End Function


Was This Post Helpful? 0
  • +
  • -

#5 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4312
  • View blog
  • Posts: 7,468
  • Joined: 08-June 10

Posted 26 October 2012 - 10:04 AM

Yes, I can explain your issue. The problem is that you're including the ' character as part of your parameter's value. You do not need to do this, and in fact, must not do this. By doing this, you're telling SQL to literally match something that includes an apostrophe at the beginning and end.

You should replace that line with this:

SQLCmd.Parameters.AddWithValue("@partial", "%" & param & "%")

Was This Post Helpful? 1
  • +
  • -

#6 maddtechwf  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 41
  • Joined: 22-October 12

Posted 29 October 2012 - 07:09 AM

View PostCurtis Rutland, on 26 October 2012 - 11:04 AM, said:

Yes, I can explain your issue. The problem is that you're including the ' character as part of your parameter's value. You do not need to do this, and in fact, must not do this. By doing this, you're telling SQL to literally match something that includes an apostrophe at the beginning and end.

You should replace that line with this:

SQLCmd.Parameters.AddWithValue("@partial", "%" & param & "%")


Thank you so much for that. That fixed my issue.
Was This Post Helpful? 0
  • +
  • -

#7 Mountain_That_Rides  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 31-October 12

Posted 01 November 2012 - 08:06 AM

If you do not have an adversity to using LINQ to SQL you could utilize a custom function in your C# application to pass the needed parameters that way. Here is a link to an example that performs the insert to the database using parameters. Just a thought. Hope you find a solution to your current issue.

LINQ and Custom Functions
Was This Post Helpful? 0
  • +
  • -

#8 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4312
  • View blog
  • Posts: 7,468
  • Joined: 08-June 10

Posted 01 November 2012 - 08:19 AM

I don't really see the point there. His issue was quite simple, he was including a quote mark in the parameter. Parameters are escaped, so that quote was treated as a literal quote rather than a string identifier. He's already solved it.

If you're going to go with LINQ to SQL (or EF) go all the way. You can insert into those tables quite easily if you're using the generated classes.
Was This Post Helpful? 0
  • +
  • -

#9 tlhIn`toq  Icon User is online

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,366
  • Joined: 02-June 10

Posted 16 November 2012 - 11:31 AM

After an hour of thinking I was just blind and missing something I came across this:

            if (OpenConnection())
            {

                var query = "USE " + DatabaseName;
                var cmd = new MySqlCommand(query, _connection);
                cmd.ExecuteNonQuery();

                // query = "SELECT * FROM dork WHERE id = 2"; // confirmed this works
                // query = "SELECT * FROM @tablename WHERE id = @id"; // Syntax error near 'dork' WHERE id = 2
                // query = "SELECT * FROM @tablename WHERE id = 2"; // Syntax error near 'dork' WHERE id = 2
                query = "SELECT * FROM dork WHERE id = @id"; // this works.  So it seems you can't parameterize the table name.


                using (var command = new MySql.Data.MySqlClient.MySqlCommand(query, _connection))
                {
                    command.Parameters.AddWithValue("@id", ID);
                    //command.Parameters.AddWithValue("@tablename", TableName);
                    //command.Parameters.AddWithValue("@tablename", "dork");

                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var p = new Person
                            {
                                ID = Convert.ToInt32(reader["id"]),
                                NameFirst = reader["name"] as string,
                                DLnumber = reader["age"] as string
                            };
                            list.Add(p);
                        }
                    }
                }




Is it just me doing something wrong, or is it MySQL, or can you simply not parameterize the table name?

I'm trying to teach myself database accessing from C# - While still following OOP concepts and abstract away the GUI from the database. Which I'm finding hard to do because the nature of the queries seems very tightly tied. But I thought I could put together a layer to act between the GUI and the database and give it methods for things like GetItem(string tablename, int id) - but that doesn't seem to work (lines 08-10). So that leaves lots of methods such as GetPerson, GetCar, GetGun and so on that are each hard coded with the table name. I can't believe it has to be that way. Am I missing something about parameterizing the table name to be used?
Was This Post Helpful? 0
  • +
  • -

#10 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4312
  • View blog
  • Posts: 7,468
  • Joined: 08-June 10

Posted 16 November 2012 - 01:32 PM

You actually can't parameterize identifiers. That's kinda like trying to use a string to refer to a variable name.

Parameters are actually somewhat restrictive. The absolute best way to deal with what you're doing is to completely separate your query from your code, using Stored Procedures. That way, all you have to do is include procedure names in your config file, and if you want to change the way the query is processed, you can change it in the database, and not have to recompile, as long as the output doesn't change.
Was This Post Helpful? 1
  • +
  • -

#11 tlhIn`toq  Icon User is online

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,366
  • Joined: 02-June 10

Posted 16 November 2012 - 02:53 PM

Hmmm... I'm picturing a WPF Window with several user controls that each present a group of information. And each usercontrol is (more or less) the presentation of a table row.

----------------------------------
UserControl with info on Person
Name, phone, address etc.
----------------------------------
UserControl with info on Cars
belonging to above Person
----------------------------------
UserControl with info on Pets
belonging to above Person
----------------------------------


Now, when it comes time to do a search I want the user to be able to enter as much or as little to narrow down the search.
So if they provide the Person's first name of Fred and a Pet name of Dino, then we should get the user "Fred Flintstone"

My plan was to prefill all search fields with "*", then overwrite that with the data they provide on the Window GUIs. So the query would basically read:
SELECT * FROM people WHERE firstname = "fred" lastname = "*" street="*" phone="*"
and so on with the asterisks except where a criteria was given. Is this do-able or does it make no sense at all?

So I'm new enough to not know if this idea lends itself to a Stored Procedure. I haven't read and experimented far enough yet.
Step 1: Simple proof-of-concept for talking to MySQL with existing data
Step 2: Get where I can add data using hardcoded infor
Step 3: Get where I can add data using GUI values
Step 4: Parameterize the string-based interaction.

That's as far as I've gotten in my self teaching - using yours and other tutorials and on-line MySQL documentation.
Was This Post Helpful? 0
  • +
  • -

#12 tlhIn`toq  Icon User is online

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,366
  • Joined: 02-June 10

Posted 21 November 2012 - 02:15 PM

Just wondering, is there a way to put a link (shortcut) in the database tutorials section to this article?

It would be good if this great tutorial could be found in both sections.
Was This Post Helpful? 0
  • +
  • -

#13 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4312
  • View blog
  • Posts: 7,468
  • Joined: 08-June 10

Posted 21 November 2012 - 02:35 PM

I'll look into that. This tutorial is more geared towards C# development, but I think the concept would be good to expand upon. Maybe we can collaborate with several other language experts and make a "parameterization guide" that includes techniques from several languages.
Was This Post Helpful? 1
  • +
  • -

#14 albert018  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 22
  • Joined: 07-January 13

Posted 12 January 2013 - 07:14 AM

Good article.Actually the most projects I've attended usually take advantage of string concatenation. We always get this problem that clients type in the character「'」 . Perhaps next time we'll try this way.
By the way. Is it a good idea to use Linq to Sql instead of ADO.NET? It seems like Linq is more powerful than ADO.NET ( personal think ).Do you have suggestion about when the good timing is to implement Linq in a project ??
Was This Post Helpful? 0
  • +
  • -

#15 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4312
  • View blog
  • Posts: 7,468
  • Joined: 08-June 10

Posted 12 January 2013 - 10:43 AM

Depends. Entity Framework would be the one I used, since LINQ to SQL isn't supported going forward (it was a stop-gap project they released while EF was being worked on). But I honestly think it depends on your comfort with LINQ and your database requirements.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2