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

"Am I right that SQL injection can happen ONLY with POST or GET methods?" No. You can inject SQL in cookies too. — Tchoupi, Aug 13 '12 at 19:54
Interesting, but it implies affecting to the global variable? Is this really possible? Could you be kind provide an example, please? — Ilia Ross, Aug 13 '12 at 19:56
Yes. A perfect example is a language cookie. `$_COOKIE['lng'] = 'en-en'`. I can change my cookie to `en'; SELECT SLEEP(20);`. Then while querying the language in database, the query will get stuck for a while. — Tchoupi, Aug 13 '12 at 20:01
Indeed great example! So the cookie must also be escaped when it's sent to db? I use this, is this good enough: `$username = mysql_real_escape_string($_COOKIE['current_session']['username']);` — Ilia Ross, Aug 13 '12 at 20:09
Yes. As phihag answered, don't try to guess if it's user input or not. Even if today you use a static value, next week you might replace the static value with a user input, and forget about it. So use phihag's advice, and escape everything. — Tchoupi, Aug 13 '12 at 20:11

phihag · Accepted Answer · 2012-08-13T20:05:33.150

10

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.

edited Aug 13 '12 at 20:05

answered Aug 13 '12 at 20:00

phihag

278,196
72
453
469

Nice to hear this! Thank you very much for answering! :) – Ilia Ross Aug 13 '12 at 20:06
"and one time is enough for a skilled attacker"! hmm .. but the attacker doesn't see the source code? – Ilia Ross Aug 13 '12 at 20:10
1

@IliaRostovtsev They don't see the code. But don't worry, they will try every form, cookie (session cookie as well), ajax, basically anything that could be used to inject SQL. – Tchoupi Aug 13 '12 at 20:14
@IliaRostovtsev The attacker doesn't know the php code, but by looking at the forms and code, he knows the names of the GET/POST parameters. And he can just [automatically try all of them](http://maestro-sec.com/blogs/2008/10/top-15-sql-injection-scanner/). – phihag Aug 13 '12 at 20:15
Is there a way to monitor all of this without writing any special codes for creating logs? Is there a known way of doing this? – Ilia Ross Aug 13 '12 at 20:16
1

@IliaRostovtsev Sorry, but I don't exactly know what you mean by `all of this`, but it seems like you want an [IDS](http://en.wikipedia.org/wiki/Intrusion_Detection_System). – phihag Aug 13 '12 at 20:18
@phihag This is exactly what I wanted to ask! I will read and keep learning! Many thanks again! ;) – Ilia Ross Aug 13 '12 at 20:22
1

In response to phihag's statement "Therefore, you may want to use the modern PDO with prepared statements instead of adodb.", I'd like to point out that ADOdb is perfectly capable of executing parameterized queries, e.g. $sql = 'insert into table (col1,col2) values ('.$DB->Param('a').','.$DB->Param('b').')'; $stmt = $DB->Prepare($sql); $stmt = $DB->Execute($stmt,array('one','two')); – dregad Nov 29 '15 at 00:36

score 2 · Answer 2 · edited May 23 '17 at 11:43

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.

SQL injections in ADOdb and general website security

2 Answers2

From YouTube

From Wikipedia

From OWASP

From PHP Manual

From Microsoft and Oracle

Stack Overflow

SQL injection scanner

Linked