Databases: Security

From Wikibooks, open books for an open world
Jump to navigation Jump to search

UNIT 3 - ⇑ Databases ⇑

← Data definition language Security


SQL injections[edit | edit source]

Badly formed SQL can offer crackers a way to read and edit data on your server through something called an SQL Injection attack. This involves an SQL statement in the code behind a web page being made up of one or more variables defined by user input. For example when you are searching for a users login information on an online shopping catalogue, the code behind the items being returned might be like this:

  1. Fetch the data input by the user on the web form
  2. Construct SQL for query by concatenating user input: sql = "SELECT * FROM users WHERE ID = " . user_ID
  3. Perform Query

This may look absolutely fine if the user inputs 67 or something similar:

SELECT * FROM users WHERE ID = 67

But if the user is a criminal and guesses what your SQL statement looks like, they can inject some malicious SQL. For example instead of inserting 67, they might insert 67 OR 1 = 1 instead. This would make make the server list every row as 1 always equals 1. The attacker now has access to all of your customers details which may include addresses and payment details.

Luckily for numeric values this is very easy to fix by using validation.

  1. Fetch the data input by the user on the web form
  2. Check that the user input contains only numbers
  3. Construct SQL for query by concatenating user input: sql = "SELECT * FROM users WHERE ID = " . user_ID
  4. Perform Query

This means that the user input cannot contain anything that the SQL server would interpret as executable code. Stupidly, even big firms still suffer from SQL injections.

For example when you are searching for a users name and password on an online banking site, the code behind the items being returned might be like this:

  1. Fetch the data input by the user on the web form
  2. Construct SQL for query by concatenating user input: sql = "SELECT * FROM users WHERE Name = \"" . user_name . " \"AND Password = \"" . password . "\""
  3. Perform Query

Here the attacker could enter attackerpass" OR "" = " for the password and access to everyone accounts as the sql would be "SELECT * FROM users WHERE Name = "attacker" AND Password = "attackerpass" OR ""="" .

However a better solution to this is to used parameterised queries. With parameterised queries the SQL is sent to the database separately form the SQL and is added into the query after the database has interpreted the instructions so cannot contain executable code.

For example when you are searching for an users name and password on an online banking site, the code behind the items being returned might be like this:

  1. Fetch the data input by the user on the web form
  2. Construct SQL for query without user input: sql = "SELECT * FROM users WHERE Name = @username AND Password = @password
  3. Perform Query

Here if the attacker entered attackerpass" OR "" = "" the database would search for a user with name attacker and password attackerpass" OR "" = "" Make sure when designing your code you don't fall victim to this.

Storing passwords[edit | edit source]

Hashing passwords[edit | edit source]

No system should ever be considered secure, however well you think you have coded it. One thing that people really don't like having criminals gain access to are passwords. You might be tempted to make a database like this:

Field Data type Example
ID Integer 12
Firstname string George
Surname string Burridge
Password string password123

Let's ignore the fact that the password is rubbish, it is never a good idea to leave passwords as plain text. If the database was cracked then the cracker would have access to all the plain text passwords and could use these to crack into other systems (people tend to use the same password in multiple locations). You should instead use a hash of the password through a system such as sha2. For more information about hashing see Hashing. This means that if the attacker has the hash it would take too long to work out the password to use it in most circumstances.

Salting[edit | edit source]

However if you are planning to hack a large number of hashed passwords you can use a rainbow table which is a precomputed hash table of the passwords. These can be generated relatively quickly for short length passwords. Many are available online for common hash functions to certain lengths. This can be prevented by using salting. Salting is the process of adding a short random string on the front of the password and then storing it alongside the password. For example:

Field Data type Example
ID Integer 12
Firstname string George
Surname string Burridge
PasswordHash string GF3H£*24(!!97
Salt string AKF*67K@

This may seem to not improve security but effectively prevents the use of rainbow tables as you would have to construct the table for each salt.


Further reading[edit | edit source]