Database - Why should I use Parameters instead of putting values into my SQL string?-VBForums
Results 1 to 3 of 3

Thread: Database - Why should I use Parameters instead of putting values into my SQL string?

Threaded View

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    36,812

    Database - Why should I use Parameters instead of putting values into my SQL string?

    When you build SQL statements, it is natural to put values into them as you would normally build other strings, eg:
    strSQL = "SELECT * FROM table1 WHERE field1 = '" & txtValue.txt & "'"

    However there are several reasons why that is a bad idea, and a better way to do it.

    The reasons why string building methods are bad include the following:
    It is awkward to write/read/maintain
    When you have a long line of string buliding code (even if you spread it across multiple lines), it can be difficult to get the right amount of quotes etc, and when you come back to it later it can be hard to make modifications correctly.

    It is easy to forget to format/delimit values as apt for their Data Type
    Values in SQL statements need to be formatted and/or delimited in a certain way, otherwise they will cause errors or bugs - particularly when it comes to Date values (the errors/bugs may not appear on your computer, but then do on others).

    There is an explanation of how values should be formatted/delimited in the article How do I use values (numbers, strings, dates) in SQL statements?, however it can be easy to overlook this somewhere in a long SQL statement.

    Your code is much more specific to the database system you are currently using (eg: Access or SQL Server)
    Unfortunately each database system has its own variation of the SQL language, and there are several differences between them. The most common difference is the way that values should be formatted/delimited, which you now have in a long line of code embedded with the rest of the statement.

    If you want to change to a different database system, the chances are that you will need to re-write (or drastically modify) a reasonable large/complex section of code for each SQL statement.

    You are likely to get errors due to special characters
    A common question we see is how to deal with the ' character in string values (eg: a surname of O'Brien), as the database system assumes that ' marks the end of the value, and therefore thinks the rest (in this case Brien) is meant to be part of the SQL itself rather than a value - and as it isn't valid, you get an error.

    There are well known ways to deal with that particular issue, however there are many other special characters too - and they vary by database system.

    Are you aware of all of the special characters (and character combinations) for the database system you are using, and have you dealt with all of them in the appropriate way? If you haven't, it is only a matter of time and/or luck until you have problems because of it.

    You are prone to SQL Injection attacks
    This is basically an extension of the previous item, but is done intentionally (by a user or program) to cause damage and/or breach security - with far more significant effects than getting errors/bugs.

    Depending on the circumstances, the following are some of the possible outcomes of an injection attack:
    • somebody can log into your program when they shouldn't being able to.
    • all of the data in your database can be read.
    • your data/tables/etc can be deleted or damaged.
    • the security settings of the database can be changed so that nobody (not even you) can get to the data - or the opposite, so that everyone can get to it.
    • if your application is web based, your entire site (not just the pages produced by your code) can be changed.
    • somebody can take control of the entire computer the database is on, and if apt even the entire network that the computer is in.
    There are several ways to do an injection attack, and I will not go into them - as doing so would help people who have malicious intentions.

    However, you can easily check if you are vulnerable.. first of all, if you enter a value (via whatever input methods you give the user) which contains a ' character (eg: O'Brien), do you get an error? If so, you are prone to an attack.

    If not, do you have code which specifically deals with the ' character? If so you are still prone to attack, unless you have dealt with every other special character (and character combination) too. Which characters are special (and how you would need to deal with them) varies by database system, and even between different versions of the same database system. If you don't deal with all of them in the apt ways, an attacker can find a way in.

    If you want more information about SQL Injection, you can see some at Wikipedia, but note that (like most sites) it intentionally avoids giving much detail.


    These problems are all removed (or in one case, "just" significantly reduced) by using Parameterised queries, which most database access technologies (such as ADO) provide for you.

    The benefits of Parameterised queries include:
    When you use parameters, your SQL statement just contains placeholders instead of the values, so a statement which would be like this for string building:
    strSQL = "SELECT * FROM table1 WHERE username = '" & txtUserName.txt & "' AND pass = '" & txtPass.txt & "'"
    ..becomes something like this when using parameters:
    strSQL = "SELECT * FROM table1 WHERE username = ? AND pass = ?"
    This is clearly easier to write/read, and it does much more too.

    An important thing to notice is that the values and delimiters have been removed. Any delimiting and formatting that is needed will be done automatically for you, using the right method for the database system you are connected to - so not only do you avoid that work, but the code will also be fine for other database systems and other versions of the same database system (you may need to change other parts of the SQL statement, but only if you would have needed to anyway).

    All of the special characters are also dealt with automatically (again, using the right method for the database system you are connected to), so you don't have to think about the ' character etc, or worry about injection attacks.


    How you use Parameterised queries depends on which language and database access technology you are using.

    If you are using ADO in Classic VB, there is a full explanation in the article How do I use an ADO Command object?

    There are also some examples in the following FAQ/CodeBank articles:
    Classic VB/ADO: How can I add a record to a database?
    (example for an Insert statement - method 2 shows string building, method 3 shows the same statement with parameters).

    VB.Net/ADO.Net: Retrieving and Saving Data in Databases
    (various examples - any snippets which contain the keyword Parameters)
    If you want to make your code even less specific to the database system you are currently using, see this CodeBank thread.

    C#/ADO.Net: Retrieving and Saving Data in Databases
    (various examples - any snippets which contain the keyword Parameters)
    Note that while using parameters means a few extra lines of code, it saves effort overall (as you don't need to deal with delimiting and formatting, or the ' character, etc), and it is much more reliable.
    Last edited by si_the_geek; Feb 1st, 2010 at 02:46 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.