SQL injections in ADOdb and general website security

Question 1

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

Question 2

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.

Question 3

I've added the Initial Catalog to your connection string. I've also abandonded the ADODB.Command syntax in favor of simply creating my own SQL statement and open the recordset on that variable.

Hope this helps.

Sub GetDataFromADO()
    'Declare variables'
        Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String

    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;"
        objMyConn.Open

    'Set and Excecute SQL Command'
        strSQL = "select * from myTable"

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open strSQL            

    'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

End Sub

Question 4

Could someone tell me if it is secure or if it is vulnerable to the SQL Injection attack or other SQL attacks ?

As uri2x says, see SQL injection that gets around mysql_real_escape_string().

The best way to prevent SQL injection is to use prepared statements. They separate the data (your parameters) from the instructions (the SQL query string) and doesn't leave any room for the data to contaminate the structure of your query. Prepared statements solve one of the fundamental problems of application security.

For situation where you cannot use prepared statements (e.g. LIMIT), using a very strict whitelist for each specific purpose is the only way to guarantee security.

// This is a string literal whitelist
switch ($sortby) {
    case 'column_b':
    case 'col_c':
        // If it literally matches here, it's safe to use
        break;
    default:
        $sortby = 'rowid';
}

// Only numeric characters will pass through this part of the code thanks to type casting
$start = (int) $start;
$howmany = (int) $howmany;
if ($start < 0) {
    $start = 0;
}
if ($howmany < 1) {
    $howmany = 1;
}

// The actual query execution
$stmt = $db->prepare(
    "SELECT * FROM table WHERE col = ? ORDER BY {$sortby} ASC LIMIT {$start}, {$howmany}"
);
$stmt->execute(['value']);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

I posit that the above code is immune to SQL injection, even in obscure edge cases. If you're using MySQL, make sure you turn emulated prepares off.

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Question 5

When you run a batch of commands using ADODB, I believe it runs each one seperately. To force the next command to run, you have to use the following:

Set rs = rs.NextRecordset()

Changing the end of your routine to the following should do the trick:

Set rs = New ADODB.Recordset
rs.Open SQLStr, cn, adOpenKeyset, adLockOptimistic
Set rs = rs.NextRecordset
MsgBox (rs.Fields(0).Value)

Question 6

Skip the old mysql_* stuff if you can and use PDO.

$pdo = new PDO('mysql:host=localhost;dbname=whatever', $username, $password);

$statement = $pdo->prepare('SELECT * FROM table WHERE pid=:pid AND ID=:id');

$statement->bindParam(':pid', $_GET['pid']);

$statement->bindParam(':id', $_GET['id']);

$results = $statement->execute();

var_dump($results->fetchAll());

SQL injections in ADOdb and general website security

Answers

php,mysql,security,sql-injection,adodb