Efficently preparing and binding statements?

[thekeemo]

Out of laziness and stupidity I used $_POST, $_GET and $_SESSION directly thinking that all I would need to prevent sql injection later on is put them in something like this prepare($_POST['thing']) and that would be it

upon doing more research I discovered that it is not so simple and quite alot more involved

The question is how can fix this mistake very late into the development process.

I am using mysqli not PDO

[Mr_KoKa]

You mean you did something like this?

$query = 'SELECT * FROM `table` WHERE `id` = '.$_POST['id'];

And used it in query, and you had in plan to switch query with prepare?

Or there something more into it I missed and you will explain?  

 

But any way, to bind a parameter you use prepare like you said and instead concatenate a parameter, you place "?" sign in it's place. So above example will look like:

$stmt = $mysqli->prepare('SELECT * FROM `table` WHERE `id` = ?');

And then when query has no syntax errors and such then you will have your $stmt whihch is mysqli statement and you can use bind_param like:

$stmt->bind_param('i', $_POST['id']);

the first argument of bind_param method is string that tells of what type are next paramter(s) so it can look like this:

$stmt = $mysqli->prepare('SELECT * FROM `table` WHERE `word` = ?, `number` = ?, `id` = ?');
$stmt->bind_param('sii', 'Hello', 1337, 123);

It will bind 'Hello' for `word` = ?  and 1337 for number, 123 for id, so order matters.

 

So what it all does, it send separately the query, and parameters to the mysql server, so the server has no doubts what is query and what is data, so there is not any magic to it, and the idea of using function on whole query with data that will guess what is data and what is query would be difficult I guess.

Sorry if it wasn't what you are asking about and if I just explained to you something you already know.

 

PS. to execute such prepared query you call execute() method on $stmt, you may want do some error checking and such.

[thekeemo]

1 hour ago, Mr_KoKa said:

You mean you did something like this?

$query = 'SELECT * FROM `table` WHERE `id` = '.$_POST['id'];

 

pretty much..

I was hoping there would be a way where I could just go cntrl + shit + r put values in and press enter but I guess this is the only way. You did tell me something I knew but you clarified to me how it works so thank you.

the project is essentially done just need to prepare the statements and fix some other security holes

[vorticalbox]

I like to use PDO and classes to prepare statements.

 

DB.php

<?php
    class db {    
        private static $handle;    
        public static function connect($host, $username, $password, $db){        
            self::$handle = new PDO("mysql:host={$host};dbname={$db}", $username, $password);        
            self::$handle->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );    
        }    
        public static function query($sql, $args=null){        
            $sth = self::$handle->prepare($sql);        
            $sth->execute($args);        
            return $sth;    
        }
}
?>

page.php

 

<?php
include('DB.php');
db::connect("127.0.0.1", "root", "securepassword", "database");
$sql = db::query ("SELECT * FROM posts WHERE 'id' = :i",
            array(":i" => $_POST['ID']));
if($sql){
// rest of code
}
?>

 

[thekeemo]

4 hours ago, vorticalbox said:

I like to use PDO and classes to prepare statements.

 

DB.php

<?php
    class db {    
        private static $handle;    
        public static function connect($host, $username, $password, $db){        
            self::$handle = new PDO("mysql:host={$host};dbname={$db}", $username, $password);        
            self::$handle->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );    
        }    
        public static function query($sql, $args=null){        
            $sth = self::$handle->prepare($sql);        
            $sth->execute($args);        
            return $sth;    
        }
}
?>

page.php

 

<?php
include('DB.php');
db::connect("127.0.0.1", "root", "securepassword", "database");
$sql = db::query ("SELECT * FROM posts WHERE 'id' = :i",
            array(":i" => $_POST['ID']));
if($sql){
// rest of code
}
?>

 

That's even more work because everything is sqli.

[vorticalbox]

39 minutes ago, thekeemo said:

That's even more work because everything is sqli.

maybe not if you have say $result = sql ****** you just need to replace it with the sql part I posted rather than adding all the binding parts as they are done in the class.

[thekeemo]

4 hours ago, vorticalbox said:

maybe not if you have say $result = sql ****** you just need to replace it with the sql part I posted rather than adding all the binding parts as they are done in the class.

I do..

[thekeemo]

On 29/08/2016 at 8:12 AM, vorticalbox said:

maybe not if you have say $result = sql ****** you just need to replace it with the sql part I posted rather than adding all the binding parts as they are done in the class.

Any idea why the code below won't work?

On 28/08/2016 at 10:25 PM, Mr_KoKa said:

 

$conn = new mysqli($SERVER, $USER, $PASS, $DATABASE);
// prepare and bind
$stmt = $conn->prepare("INSERT INTO unauthorized_log (HTTP_CLIENT_IP,HTTP_X_FORWARDED_FOR,REMOTE_ADDR) VALUES (?,?,?)");
$stmt->bind_param("sss", $client, $http, $remote);
$client = $_SERVER['HTTP_CLIENT_IP'];
$http = $_SERVER['HTTP_X_FORWARDED_FOR'];
$remote = $_SERVER['REMOTE_ADDR'];
$stmt->execute();

[Mr_KoKa]

What do you mean by not working, does it not insert new row or there is some php error, add some error handling, chceck if prepare isnt returning false (use !==) then you can check if bind_param returns false, and finally, if execute returns false. If prepare fails then there will be $conn->error populated with last error, if execute fails there is $stmt->error populated. bind_param emits php warning.

[thekeemo]

2 minutes ago, Mr_KoKa said:

What do you mean by not working, does it not insert new row or there is some php error, add some error handling, chceck if prepare isnt returning false (use !==) then you can check if bind_param returns false, and finally, if execute returns false. If prepare fails then there will be $conn->error populated with last error, if execute fails there is $stmt->error populated. bind_param emits php warning.

No errors no insert

[Mr_KoKa]

Add some echo every second line and see if this part of code is even run. Do you usually see an error if one is occurring? Or you may have errors disabled?

[thekeemo]

3 hours ago, Mr_KoKa said:

Add some echo every second line and see if this part of code is even run. Do you usually see an error if one is occurring? Or you may have errors disabled?

Its running.

I had a non prepared statement in there before

[Mr_KoKa]

$conn = new mysqli($SERVER, $USER, $PASS, $DATABASE);
// prepare and bind
$stmt = $conn->prepare("INSERT INTO unauthorized_log (HTTP_CLIENT_IP,HTTP_X_FORWARDED_FOR,REMOTE_ADDR) VALUES (?,?,?)");
if($stmt !== false){
  if($stmt->bind_param("sss", $client, $http, $remote)){

    $client = $_SERVER['HTTP_CLIENT_IP'];
    $http = $_SERVER['HTTP_X_FORWARDED_FOR'];
    $remote = $_SERVER['REMOTE_ADDR'];

    if(!$stmt->execute()){
      die('Error occurred: '.$stmt->error);
    }
  } else {
    die('Error occured when binding parameters.');
  }
} else {
  die('Error occurred: '.$conn->error);
}

Try this and tell me if something is printed out.

[thekeemo]

30 minutes ago, Mr_KoKa said:

$conn = new mysqli($SERVER, $USER, $PASS, $DATABASE);
// prepare and bind
$stmt = $conn->prepare("INSERT INTO unauthorized_log (HTTP_CLIENT_IP,HTTP_X_FORWARDED_FOR,REMOTE_ADDR) VALUES (?,?,?)");
if($stmt !== false){
  if($stmt->bind_param("sss", $client, $http, $remote)){

    $client = $_SERVER['HTTP_CLIENT_IP'];
    $http = $_SERVER['HTTP_X_FORWARDED_FOR'];
    $remote = $_SERVER['REMOTE_ADDR'];

    if(!$stmt->execute()){
      die('Error occurred: '.$stmt->error);
    }
  } else {
    die('Error occured when binding parameters.');
  }
} else {
  die('Error occurred: '.$conn->error);
}

Try this and tell me if something is printed out.

Alright thanks.