Since learning how to manipulate a database is usually the first thing programmers learn it makes sense that learning about SQL injections should also be one of the first things they learn.
SQL Injections happen when a malicious user extends your SQL query by writing a bit of code into the input box. By running an unchecked query you could leave yourself open to attacks that could cripple your database.
To know how to prevent an SQL injection you need to know how programs talk to a MySQL server, it’s done with the Structured Query Language.
For the purpose of this article, there isn’t much need to dive deeply into how to use SQL, but you do need to be familiar with it.
Below is a query that will tell the server to change someone’s name to Don.
update Members set Name='Don' where ID='1'
Doesn’t look to complex does it. To get the server to update a record (A record in one row of information in the database) you simple start the query by saying “update”.
>From there, you need to tell the server what table (Since there can be many in one database) in the database you’ll be updating, in this example it’s the table called “Members”.
Since each table can have one or more columns (Columns are used to store information specific to a table) you need to tell the server which column you want to alter, and what you want new information you want to enter.
This is done by telling the server to set the column Name with new information (Don), but only where the unique id (The unique ID is a column created to give each record it’s own number to help identify rows easier) is equal to 1.
This query will only enter the name Don, which doesn’t make the application very dynamic. To allow people to enter a varied array of different information we use variables (A variable can contain any combination of characters).
update Members set Name='$Name' where ID='1'
This is what our SQL query looks like when we use a variable to input the new name. The variable would come from a form (It could realistically come from other sources, but most commonly it would come from a form) that someone submitted from the site.
Now if someone submitted the name Don from the form the SQL query would look identical to the first one posted. However, they could submit the name Mike, Fred or Wallace.
This is where the SQL injection comes into play. What if the user didn’t enter their name, what if they entered a little bit of code to adjust their Member privileges?
You probably noticed the single quotes (‘) around the variable and the number one. These tell the server where the information to enter into the column is, anything between the quotations, or in the case of the id, which record to update.
update Members set Name='Don' ,Userlevel='Administrator' where ID='1'
This SQL query will update the table named Members, and set the members name to Don, and their Userlevel to Administrator. This can be done with an SQL injection, as illustrated below.
Don' ,Userlevel='Administrator
Fairly easy to do isn’t it? Remember that to update the name the server is only looking for information between the two single quotations. By entering their name as Don’ ,Userlevel=’Administrator, the malicious user set their name to Don, but also extended the query by adding more code giving themselves administrator privileges.
Scripting languages like PHP will automatically place a backslash in front of single quotes entered by users. This lets the SQL server know that this is only part of the information to be added, and doesn’t allow an SQL injection. If for any reason you need to strip the slashed from user input, remember to add them again before running your query, or you may find yourself a vicitim of an SQL injection.
Douglas Brunner is the administrative director for Norics. Norics provides resources for the off-line world to get on-line, and be successful.
