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.
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.
Reply With Quote
I wish I had read this before I found out the hard way about how much of pain in the ass the ' character can be!
