As a web developer, I often read articles about hackers (from the lowly to the knowledgeable) infiltrating websites via the dreaded 'SQL Injection' method and completely taking control, changing, gaining access, or destroying the owner's data. As a fellow web developer, I'm sure you want to know how to protect against it. Well, here it is! In this article, you will find out what SQL Injection is, what you can do to protect against it, and additional recommendations that are easy to do and only makes your data more secure.
Please note: I am not an 'absolute' expert, but none of my projects have ever been hacked (yet), are SQL Injection proof (as far as I know), and I love to learn. I guarantee nothing.
What Is SQL Injection and How Is It Used?
Basically, SQL Injection is a method used against websites and applications to gain access to the website's or application's data, stored in a SQL database. SQL Injection is used to gain access to a database's information (or an entire company), to destroy a database's information, or to manipulate a database's information. It is a method used to exploit the security vulnerability of an application or website. There are different types of SQL Injection, but in this article we will only cover the basics.
Let's see how it is used, to further understand what it is. I am going to use PHP as my scripting language in these examples. You can use substitute any language(s) you use. The focus should be on the SQL commands.
Suppose you are a professional with your own business. You have created an SQL database with a table that contains all of your clients' information, that you use to send out important notifications, billing, etc. It took you an entire year to gain 50,000 very important clients. You manage your database by logging in online, as you travel, and doing whatever you need to do, directly from your website.
One day a self-proclaimed hacker stumbles upon your website. He clicks the 'Log In' button.
The hacker now has been shown every table you have in your database.
All of your information is gone.
Note: There are attempts that are much more complicated than this, and someone can spend a lot of time to get into your database, or they can even use a program to try to exploit the vulnerability of your website, database, application, etc.
This PHP function escapes special characters for use in SQL queries and protects you from attack.
Using 'mysql_query()' has additional protection against SQL Injection. A query not wrapped in 'mysql_query()' could allow a hacker to use multiple SQL commands from your 'username' field, instead of just one, which is another vulnerability. 'mysql_query()' only allows one command at a time.
In your script, you should centralize your connections to one page.
On each page that needs it, just use the 'include()' function to include the page that hosts your SQL database connection information. This would force you to create queries with the same format on every page you create, and reduces the chances of a mistake leaving a vulnerability open.
Many programming languages force you to declare variables before you can use them throughout the script. PHP does not force you to do this, however, it's a good habit to clean out your variables at the beginning of the page anyway!
Sure someone can ask, "If I'm cleaning each variable throughout the page, why should I clean the variables at the top? Aren't I doing the same thing with your recommendation?".
It is easier on you to clean variables at the beginning of the page for a few different reasons, beyond formatting.
It reduces the amount of code you have to write.
Once the variable is clean, you can use it freely throughout the page, without the fear of vulnerabilities.
It is cleaner and more organized, allows you to work easier, and avoids mistakes.
You could even go as far as creating a function to do all cleaning for you, reducing the amount you have to type further. Look at the following example.
You can have additional checks in place to guard against unnecessary processing on your server. This is achieved by adding checks to your script before you ever get to the point of running the query; only running the query when you find the data acceptable.
This is critical to learn. So many sites are penetrated because of sloppy code it's sad. Outside of XSS I would say this is the next largest vector of attack.
I'm glad the article is useful!
PHP > 5.1 ships with PDO, use it! If you have to sanitize every input variable, chances are that you forget some of them. Especially if you are working with large scale web applications.
You could use the empty() function in combination with isset() to better check your variables.