Use Parameterized Queries to Avoid SQL Injection Attacks

Updated Reverend Jim 6 5K Views

A lot of questions in the VB.NET forum are database related. Most of the code that gets posted result in one or more comments like "use parameterized queries to avoid SQL injection attacks". I won't describe the nature of a SQL injection because it is easily looked up via google. There is a lengthy article at Wikipedia. What I will do here is give two examples of how to create queries using parameters. The first example uses SQLDB and the second uses OLEDB. The major difference between the two is that SQLDB uses named parameters while OLEDB uses positional parameters.

With SQLDB you give parameters names. Any name will do but it only makes sense to use a name that relates to the type of parameter. For example, if the corresponding database field is LastName you might name the parameter @lastName or @lname (all parameters must start with "@"). When you add the parameter values you can add them in any order and the @name you use must match the @name in the query string.

With OLEDB you specify a parameter with "?". When you add the parameters you must add them in the same order in which they appear in the query. The parameter names you use can have any name. This can lead to confusion as it might lead you to believe order doesn't matter. It is for this reason I much prefer SQLDB.

Another advantage to using parameterized queries is it avoids awkward syntax when building queries. Instead of

query = "SELECT * FROM mytable WHERE Name = '" & txtName.Text & "'"

you use

query = "SELECT * FROM mytable WHERE Name = @name"

and if you need to set a name like O'Leary, instead of

query = "UPDATE mytable SET Name = '" & txtName.Text & "'"...

which would fail, or

query = "UPDATE mytable SET Name = '" & Replace(txtName.Text,"'","''") & "'"...

which is just butt ugly, the query

query = "UPDATE mytable SET Name = @name" ...

would automatically handle the embedded '

One more note - many people write queries that spew out over many lines with no formatting and no concern that the queries be human readable. Please note the format I use. The queries are spread over several lines. Fields line up making it easier to read the query with the keywords capitalized and the major ones appearing at the start of each line. The cost is a few embedded blanks. The payback is easier debugging and (important to me at least) prettier, and easier to modify code.

If you are using MS SQL and have the PUBS database installed you can try values for txtName.Text and txtPhone such as %e% and 4% to retrieve all names containing an "e" and with a phone number starting with a "4".

Ashenvale commented: Thank you so much for giving me this great tutorial :) CHEERS! +2 
Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class Form1

    Private Sub btnSQLDB_Click(sender As System.Object, e As System.EventArgs) Handles btnSQLDB.Click

        'SqlClient is optimized for SQL Server and supports named parameters

        lvwResults.Items.Clear()

        Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=PUBS;Trusted_Connection=yes;")
        Dim cmd As New SqlCommand("", con)

        cmd.CommandText = "SELECT au_lname,au_fname,phone " _
                        & "  FROM authors                 " _
                        & " WHERE au_lname LIKE @lastName " _
                        & "   AND phone    LIKE @phone    " _
                        & " ORDER BY au_lname             "

        cmd.Parameters.AddWithValue("@lastName", txtLastName.Text)
        cmd.Parameters.AddWithValue("@phone   ", txtPhone.Text)

        con.Open()
        Dim rdr As SqlDataReader = cmd.ExecuteReader

        Do While rdr.Read()
            lvwResults.Items.Add(New ListViewItem({rdr("au_lname"), rdr("au_fname"), rdr("phone")}))
        Loop

        rdr.Close()
        con.Close()

    End Sub

    Private Sub btnOLEDB_Click(sender As System.Object, e As System.EventArgs) Handles btnOLEDB.Click

        'OLEDB is more generic (can be used for different data sources) and supports
        'parameters but they are unnamed and positional

        lvwResults.Items.Clear()

        Dim con As New OleDbConnection("Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=PUBS;Trusted_Connection=Yes;Connect Timeout=15;")
        Dim cmd As New OleDbCommand("", con)

        cmd.CommandText = "SELECT au_lname,au_fname,phone " _
                        & "  FROM authors                 " _
                        & " WHERE au_lname LIKE ?         " _
                        & "   AND phone    LIKE ?         " _
                        & " ORDER BY au_lname             "

        cmd.Parameters.AddWithValue("@lastName", txtLastName.Text)
        cmd.Parameters.AddWithValue("@splunge ", txtPhone.Text)

        con.Open()
        Dim rdr As OleDbDataReader = cmd.ExecuteReader()

        Do While rdr.Read
            lvwResults.Items.Add(New ListViewItem({rdr("au_lname"), rdr("au_fname"), rdr("phone")}))
        Loop

        rdr.Close()
        con.Close()

    End Sub

End Class
Member Avatar for diafol
diafol

Please note the format I use. The queries are spread over several lines.

I'm seeing single line snippets. Or did I miss something?

Member Avatar for Reverend Jim
Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

What you are missing is somehow my large block of code got deleted when it was converted from a code snippet to a tutorial. I put it back in. Thanks for the heads up.

Edited by Reverend Jim
Member Avatar for Reverend Jim
Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

And now that the code is back in the Tutorial flag has been cleared. Oh dear.

Member Avatar for G_Waddell
G_Waddell 131 Posting Whiz in Training

Hi,

For people using a Microsoft SQL Server database:

  1. Don't build queries on the fly - use only stored procedures, it forces you to use parameters and leads on to point two:

  2. Use a dedicated SQL account for your application that has only has execute rights on the stored procedures you have written.

By using a dedicated SQL user account with only rights to execute your stored procedures even if a SQL injection attack got to your SQL server, the user account it is running under has no rights to access, alter or delete anything on the database server therefore inbuilt SQL security will come into play.

i.e. instead of this:

MyCommand.CommandText ="SELECT * FROM MyTable WHERE (MyID='" & trim(Textbox1.text) &"')"

Do this

Mycommand.CommandText = "MyStoredProcedureName"
MyCommand.CommandType = CommandType.StoredProcedure
MyCommand.Parameters.AddWithValue("@MyID", trim(Textbox1.text))

And on your SQL Server:

CREATE PROCEDURE MyStoredProcedure @MyID bigint AS
SELECT * FROM MyTable WHERE(MyID = @MyID)

The more layers of security you add, the more difficult it becomes to cause mischief...

Edited by G_Waddell because: Spelling
Member Avatar for adam_k
adam_k 239 Master Poster

Haven't we had this conversation already?
Anyway, I agree with parameterized queries but use them with stored procedures as G_Waddell is saying. Also do use user security to limit everybody to just these procedures, but I disagree with SQL user account. For me it's always Windows Security - provided you have active directory. No password will be transmitted or saved as plain text, it will be much easier to change it and you will know who has access where if you use AD groups in SQL and add/remove members as you see fit.

I'd like to broaden up this thread a bit and say that you may avoid SQL injection following the above - it's relative and as always security is an illusion- but you may give away your data by other flaws in your design.
And I'll give an example, but it's not the only one: People use datasets to retrieve all accounts in their app and then match user entry against that, because that's what they are familiar with. This is a bad practise.
Not only you are transmiting the whole security of your app over the network, you are using .Net to hold everything. It doesn't have to be SQL injection, you gave away username and password for anybody with admin rights.
It's same if you select * with criteria in the server. Instead either only select what you care for (if something is joined in your query, you will still return only what you wanted it to) or for example in user validation select count(*) and verify that the count = 1. If it's more than 1 then you've been injected.

The point I want to make is don't follow a guide and then feel safe. Think before you write, limit everything, code smart and then hope. The only safe data are the ones in a computer not connected to anything, not powered on and locked in a vault - unless....

Sorry if I messed this up.
Good luck with your apps.

Member Avatar for Reverend Jim
Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

The problem with security is that if you take all possible precuations then the app becomes

  • too time consuming to write and test
  • too expensive to develop/debug
  • too difficult to maintain

In our corporate environment I could reasonably assume that nobody was packet sniffing to ferret out credentials.

Look at airport security. If we took all possible precautions then every single item going on board would have to be examined by eye and by sniffer dogs and everyone would have to be strip searched and x-rayed. For that matter, no luggage would be allowed and people would have to surrender their clothing when boarding and be issued disposable clothing for the trip. Full body cavity searches would be mandatory to prevent against ingested or inserted explosives/weapons.

Of course, such measures would never be tolerated (then again, who could have forseen the current TSA measures). We always try to compromise between reasonable security and reasonable measures (again, TSA grope search?).

Member Avatar for G_Waddell
G_Waddell 131 Posting Whiz in Training

It really depends what you are doing and where the app will run.

For instance, if you've got ASP.net pages on a server in a DMZ opened out to the Internet you may prefer to avoid using windows security and opening your domain up to the DMZ in which case if you only open SQL Port use a SQL user AND FOR THE LOVE OF GOD NOT SA sorry, just found some code where a junior programmer had used the sa user in their test/ development machine then deployed it live to a web site @@@!!""""£££$

I've got into the habit of doing it my way and it is just second nature now.

Member Avatar for adam_k
adam_k 239 Master Poster

The IIS will run under an account. If it's under system account I believe that access is far greater than a user with limited priviledges in the local system and the domain resources and the SQL server.
About the sa account, my point exactly. People go way out of their way to protect themselves from SQL injection, but fail to see that they've got a million other security issues.

Member Avatar for savedlema
savedlema 17 Junior Poster

I find this to be useful tutorial to me, I've just come into a problem while inserting a person's name which has an ' in it.

Will this code provided by Jim work in VB.NET + Access? or its just for SQL?
Using VB.NET with Access, what should be wrong with this code:

cmd.CommandText = "INSERT INTO tblDetails (StudentID, FirstName) " _
                    & (" VALUES @StudentID, @FirstName ")

            cmd.Parameters.AddWithValue("@StudentID ", txtID.Text)
            cmd.Parameters.AddWithValue("@FirstName ", txtFirstName.Text)
                cmd.ExecuteNonQuery()

I get "object reference not set to an instance of an object". Why?
(I hope I didn't offend you by posting this in this thread)

Member Avatar for Reverend Jim
Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

This thread is for code snippets. Please post this as a regular forum thread and I will be happy to respond. Please include the code from the entire section (Function or Sum) and please indicate what line is causing the error.

Edited by Reverend Jim
Member Avatar for savedlema
savedlema 17 Junior Poster

I got through it already. But, I would like to thank you Rev a lot. I had gotten into trouble when a name with an ' would be inserted. I think I'll be looking forward into learning some other SQL Injection prevention methods. Thumbs up for you and other contributors here too!

Member Avatar for pearl.kumar1
pearl.kumar1 0 Light Poster

I don't Understand Why you are using this line ...

 i.cmd.CommandText = "SELECT au_lname,au_fname,phone " _
                    & "  FROM authors                 " _
                    & " WHERE au_lname like @lastName " _
                    & "   AND phone    like @phone    " _
                    & " ORDER BY au_lname             "
    cmd.Parameters.AddWithValue("@lastName", txtLastName.Text)
    cmd.Parameters.AddWithValue("@phone   ", txtPhone.Text)

ii cmd.Parameters.AddWithValue("@lastName", txtLastName.Text)
cmd.Parameters.AddWithValue("@phone ", txtPhone.Text)
cmd.CommandType = CommandType.StoredProcedure

May i know the difference between this two Syntax?
Edited by pearl.kumar1 because: test
Member Avatar for Reverend Jim
Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

I don't use the line

i.cmd.CommandText = "SELECT au_lname,au_fname,phone " _

The reason I don't use

cmd.CommandType = CommandType.StoredProcedure

is because I am not executing a stored procedure.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview