Viewed   134 times

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 
           name='" . mysql_real_escape_string($name) . "',
           mail='" . mysql_real_escape_string($mail) . "', 
           password='" . mysql_real_escape_string(sha1($password)) . "'";

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

$sql = 
        videoID = '" .mysql_real_escape_string($videoID). "' AND UID = '" .mysql_real_escape_string($userID). "' LIMIT 1";

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

Thanks in advance,



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.

Tuesday, September 27, 2022

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;"

    '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
Saturday, September 17, 2022

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
        $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}"
$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);
Tuesday, September 20, 2022

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)
Saturday, November 5, 2022

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();

Monday, November 28, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :