About SQL injection

    Despite this kind of attack is very old, it is still the primary source of database and website attacks so it really deserves a little speak here and everywhere. In an ideal world, maybe this will not worth a cent, but unfortunatelly we are not in an ideal world.

    Many people say that this is due to malicious people and/or hackers fault, but the guilt comes from both sides: bad programmers and bad people.

    I'll explain this kind of attack with an example (in PHP, but it affects SQL independently of language used).

    Suppose we have a login validation this way:

$sentence="SELECT COUNT(*) FROM Users WHERE username='".$_GET['user']."' AND pass='".$_GET['password']."'";

    (And later in your code check if result>0 in order to check if login was successfull)

    The problem here comes from not checking user inputs, in a good case, sentence will not be a special one, for example: (user input in bold) 

SELECT COUNT(*) FROM Users WHERE username='bob' AND pass='foo';

    But there is a problem, if input is not sanitized, this SQL sentence could be transformed and executed into many SQL sentences allowing a user, for example this can happen because of not sanitizing _GET array:

(1) SELECT COUNT(*) FROM Users WHERE username='bob' OR 1=1;--' AND pass='foo';
or
(2) SELECT COUNT(*) FROM Users WHERE username='bob'; DROP TABLE Users;--' AND PASS='foo';
(3) SELECT COUNT(*) FROM Users WHERE username='bob'; SELECT creditcardnumber FROM Users;--' AND pass='foo';

    Those two sentences with user input marked as bold are self explanatory, but you can see how 1 sentence can be modified to allow you to login with an account which is not yours(1), to destroy data(2) or to gather other kind of sensitive information(3).

    Maybe you are thinking that if this problem is old, it does not worth to speak of it: ERROR!
From time to time, a huge company(Sony) or government agency (CIA) are attacked using this method. This leads me to think: How poor their hired programmers work, and how little do companies value our personal and sensitive data!

    So as this problem has been proved to be relevant and important, let's investigate some solutions.

• Prepared Statements and parametrized queries:

    This seems the best solution: A SQL sentence is no longer crafted by join two or more strings, and SQL keywords are splitted from data. (and also, real sentence is not sent over the network every time it is executed). An example of prepared statement could be: (in postgres)

SELECT COUNT(*) FROM Users WHERE username=$1 AND pass=$2

    This time, postgres already know what type are $1 and $2 and they are parsed accordingly, but the more important fact, $1 and $2 parameters will be treated as a complete string, eliminating the posibility of crafting another sentence from this one. So in this case a malicious input will be treated like:

SELECT COUNT(*) FROM Users WHERE username='bob''; DROP TABLE Users;--' AND pass='foo';

    In this case, as all username will be treated as a whole parameter, this sentence will likelly return 0 rows as a result instead of harming data.
Try to AVOID SQL crafted strings in your source code and use prepared statements!

• Sanitize inputs: While this approach is good enough, I recomment to apply in conjunction with previous one. It consists on checking and escaping any non standard character and convert them to avoid SQL sentence breakage. (In case you can't avoid crafting sentence)
  

$input=pg_escape_string($_GET['username']); and apply it to SQL crafted string, this way, any ' or " character that could break your SQL sentence will be quoted disallowing it to harm your sentence.

    To sum up: Even governments and big companies do not care enough their databases to spend a little time to check source code to avoid this kind of problems.