Visual Basic Programming: Avoiding SQL Injections
An SQL injection is malicious code that is inserted into user-input variables for execution by SQL Server. A Website under attack can experience more than one hacking attempt per second.
Despite awareness of security breaches caused by SQL injection in the past, it continues to be the most frequent technique used by hackers to invade a company's website. In 2011 alone, 855 thefts occurred in which 174 million records were stolen.
Equally disturbing, the detection of a security breach is often inadequate.
Foreign spies should be assumed to have penetrated the computer networks of the US military (BBC World News).
Most Websites have inadequate security
The loss of data and confidence in Websites has cost the corporate world billions of dollars.
ASP.NET (used in this Website) runs Server-side scripting on the Web server to generate each Web page dynamically – so Client-side hacking cannot arise.
The Security Threat
There are two main types of SQL injection vulnerability:
- Query Strings added to the URL of a Website (i.e. a "?id=" in the URL)
- SQL statements inserted into an Input box of a Web page
Using these techniques, hackers can remotely retrieve passwords and confidential data, destroy a database or log in as an administrator.
The SQL injections can take place whatever the technology used. Stored Procedures do not prevent an attack nor does parameterised data. Injection attacks can even occur over HTTPS Secure Socket Layer (SSL) connections.
Input Field Validity Checks
Using user text input directly in a SQL Server SELECT statement is an open invitation to hackers – as are free form searches.
Assume that ALL Input from a user is malevolent. So input to a Website must be sanitised to ensure that the text or Query String does not contain executable code.
The Visual Basic Programmer can minimise the security risk with these essential Server-side validity checks:
- Never build SQL statements directly from user input.
- Use Visual Basic and not Stored Procedures for validity checking
- Restrict the length of each entered field
- Reject spurious characters, especially > < , - _ ; @ $ % + * = \ ? #
- Ensure that numeric fields contain valid numerics and are in range
- Ensure that date fields contain valid dates and are in range
- Ensure Email and URL addresses have the correct structure.
- Encode Query Strings
The apostrophe (single quote) needs special treatment:
- Reject any field containing more than one apostrophe
- Valid names like O'Brien will need to be changed to O''Brien to form a valid SQL literal string
Terminate the entire transaction if Input fields containing any of these words:
char, nchar, varchar, nvarchar, cast, cursor, declare, exec, execute, kill, objects, sys, sysobjects, syscolumns
These validity rules should prevent any introduced SQL statements from granting the hacker access to your SQL Server Database.
Additional Security Measures
- Sensitive data, like passwords and credit card numbers, should always be encrypted in a database. It is safest to use one-way hashing for passwords.
- Grant the website routine the least Table permissions necessary. If only reads are done, then disable record Inserts, Updates and Deletes.
- Ensure that only programmer generated error messages are displayed.
- Ensure that there is a time-out on each Web page.