SQL injections in ADOdb and general website security

Question

I have done pretty much reading and still don't understand 100% how some of the SQL injections happen!

I'd like to see, from those who know, concrete examples of SQL injection based on my example, so it could be replicated, tested and fixed. I have tried to SQL inject my code and couldn't, so I'd like someone to prove me otherwise!

1.Am I right that SQL injection can happen ONLY with POST or GET methods, meaning that on the website it should be the post form, e.g. 'signup or search' or query like 'search.php?tags=love'?

Saying that is this possible to inject the following code that has POST method?

$name     = trim($_POST['username']);
$mail     = trim($_POST['email']);
$password = trim($_POST['password ']);

   if ($errors == "false") {
    $sql = 
        "INSERT INTO 
           clients 
         SET 
           name='" . mysql_real_escape_string($name) . "',
           mail='" . mysql_real_escape_string($mail) . "', 
           password='" . mysql_real_escape_string(sha1($password)) . "'";
           $connection->execute($sql);
        
    }

2.The other one has GET method: rate.php?like&videoID=250&userID=30

$sql = 
    "SELECT 
        videoID 
     FROM 
        likes 
     WHERE 
        videoID = '" .mysql_real_escape_string($videoID). "' AND UID = '" .mysql_real_escape_string($userID). "' LIMIT 1";
        $connection->execute($sql);

Please help those that feel free with the subject but use the concrete examples.

Thanks in advance,
Ilia

Answer 1

SQL injection attacks happen when user input is improperly encoded. Typically, the user input is some data the user sends with her query, i.e. values in the $_GET, $_POST, $_COOKIE, $_REQUEST, or $_SERVER arrays. However, user input can also come from a variety of other sources, like sockets, remote websites, files, etc.. Therefore, you should really treat everything but constants (like 'foobar') as user input.

In the code you posted, mysql_real_escape_string is used to encode(=escape) user inputs. The code is therefore correct, i.e. does not allow any SQL injection attacks.

Note that it's very easy to forget the call to mysql_real_escape_string - and one time is enough for a skilled attacker! Therefore, you may want to use the modern PDO with prepared statements instead of adodb.

Answer 2

I've been investigating thoroughly on this subject recently and would like to share with others quite interesting material, thus, making my question more complete and instructive for everyone.

• Preventing SQL Injection with PHP by John Nebel
• Security Corner - SQL Injection by Chris Shiflett
• The Unexpected SQL Injection by Alexander Andonov
• Mysql_real_escape_string() versus Prepared Statements by Ilia Alshanetsky
• SQL Injection Attack and Defense by Sagar Joshi
• SQL Injection Attacks by Prof. Jim Whitehead
• addslashes() vs mysql_real_escape_string() by Chris Shiflett
• What's a SQL Injection Bug by Joel Spolsky
  
  
• MySQL - SQL injection prevention
• SQL Injection Walkthrough
• SQL Injection Cheat Sheet
• Prepared Statements in PHP and MySQLi

---

From YouTube

• SQL Injection Myths & Fallacies: Best practices of defense by Bill Karwin
• PHP Tutorials: Security - SQL Injection
• How to SQL Inject with SQLMAP on Backtrack5 RC1

---

From Wikipedia

• Wikipedia - SQL injection
• Wikipedia - SQL

---

From OWASP

• SQL Injection
• Guide to SQL Injection
• OWASP - Avoiding SQL Injection
• SQL Injection Prevention Cheat Sheet
• Testing for SQL Injection

---

From PHP Manual

• SQL Injection
• PDO class - Prepared statements and stored procedures
• MySQL Improved Extension
• mysql_real_escape_string()

---

From Microsoft and Oracle

• What's the Right Way to Prevent SQL Injection in PHP Scripts by Microsoft
• Stop SQL Injection Attacks Before They Stop You by Microsoft
• Defending Against SQL Injection Attacks by Oracle

---

Stack Overflow

• How can I prevent SQL injection in PHP?
• How does the SQL injection from the "Bobby Tables" XKCD comic work?
• https://stackoverflow.com/questions/1973/what-is-the-best-way-to-avoid-sql-injection-attacks
• What is SQL injection?
• SQL injection on INSERT
• How do I protect this function from SQL injection?
• Are Parameters really enough to prevent Sql injections?
• Is SQL injection a risk today?
• https://stackoverflow.com/questions/936254/sql-injection
• SQL Injection ethical hacking
• Does this code prevent SQL injection?

---

SQL injection scanner

• Top 15 SQL injection scanner
• Netsparker Community Edition, Free SQL Injection Scanner & XSS Scanner