-1

In my PHP document, I got a SQL query looking like this:

if(isset($_GET['id']))
{
    $id = $_GET['id'];
    $q = "SELECT * FROM `objekt_t` WHERE `id`='" . $id . "'";
    $row = mysqli_query($con, $q) or die(mysqli_error($con));
    while($r = mysqli_fetch_assoc($row))
    {
        $objekt = $r;
    }
}

I realize this is very unsafe practice concerning SQL injections and such, so I've been looking into prepared SQL querys, using bound parameters. Looking at bobby-tables.com I see this example query:

$stmt = $db->prepare('update people set name = ? where id = ?');
$stmt->bind_param('si',$name,$id);
$stmt->execute();

I do not understand how I should modify my current query to match the safer one using bound parameters. Any help is appreciated.

Simon Carlson
  • 1,919
  • 5
  • 24
  • 35

3 Answers3

1

prepared statements transmit raw data to the query so that SQL injection is not possible. There is no need to escape for real_escape_String or any other formatting functions, as this does it for you.

Example: 

$db = new mysqli ("host","user","password","database");
$statement = $db->prepare("SELECT test FROM test WHERE Username=?");
$statement->bind_param('s',$_POST['Username']);
$statement->execute();
$statement->bind_result($resultCol);
$statement->fetch();
$statement->close();

I am basically binding my $_POST data directly to the query because the data is being sent as raw, so even if the query contained a form of injection, as the $_POST['username']; the query will run as normal.

IN terms of the procedure and OOP style, it's down to preference, I personlly prefer the OOP style over the other options as it's more readable.

Working with numbers: 

$ID= 5;
$db = new mysqli ("host","user","password","database");
$statement = $db->prepare("SELECT test FROM test WHERE ID=?");
$statement->bind_param('i',$ID);
$statement->execute();
$statement->bind_result($resultCol);
$statement->fetch();
$statement->close();

Or you can work with exact values directly within the statement: 

$db = new mysqli ("host","user","password","database");
$statement = $db->prepare("SELECT test FROM test WHERE ID='5'");
$statement->execute();
$statement->bind_result($resultCol);
$statement->fetch();
$statement->close();
Daryl Gill
  • 5,464
  • 9
  • 36
  • 69
  • How to I use the result of the query? Trying `var_dump($resultCol);` gives me null. I do not understand how the result of the query can be used/called. – Simon Carlson Apr 14 '13 at 15:59
  • With my modification, and you should modify the query inside the `prepare` to return a valid result from your database. The bind_result will bind a variable to the column which is used, so if your selecting 4 columns with your query, you will need to assign 4 variables – Daryl Gill Apr 14 '13 at 16:02
  • If I for instance change the entire `prepare` line to `$db->prepare("SELECT * FROM objekt_t WHERE id=?");` and the following line to `bind_param('s','5');`, there is only 1 column that can be selected as theres only 1 column in my table where `id='5'`. Still this gives me nothing when I attempt to `var_dump($resultCol);`. I feel like I'm missing something obvious. – Simon Carlson Apr 14 '13 at 16:06
  • You are binding a string in the place of an integer, change bind_param to: `bind_param('i','5');` even know this is not recommended. See my updatE: – Daryl Gill Apr 14 '13 at 16:07
  • Still nothing is happening. If I `var_dump($statement);` using either methods, I see `["affected_rows"]=> NULL`. Do you have any idea why this is happening? How can I see the query actually happens? – Simon Carlson Apr 14 '13 at 16:14
1

Just the same way

$mysqli = new mysqli("localhost", "my_user", "my_password", "db");

if(isset($_GET['id']))
{
    $id = $_GET['id'];
    $q = "SELECT some_field FROM `objekt_t` WHERE `id`= ?";

    if ($stmt = $mysqli->prepare($q)) {    
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $stmt->bind_result($result);
        $stmt->fetch();
    }
}

Now $result variable contains the resuts of your query.

zavg
  • 10,351
  • 4
  • 44
  • 67
  • Would the result of my query be in `$stmt` or in `$result`? – Simon Carlson Apr 14 '13 at 15:45
  • I have just edited post with this remark. The $result variable contains the resuts of your query after script execution. – zavg Apr 14 '13 at 15:46
  • Attempting a `var_dump($result);` gives me NULL. Why is this? – Simon Carlson Apr 14 '13 at 15:50
  • Oh, sorry, if you use `$stmt->bind_result($result);` you can not use `SELECT *`, but just `SELECT some_field`. Or use `$stmt->bind_result($field1, $filed2);` – zavg Apr 14 '13 at 16:20
  • Still getting `var_dump($result);` as `NULL`. Do I need any extensions installed for this to work? If so, is there any way I can do it without installing extensions? – Simon Carlson Apr 14 '13 at 16:26
  • No extentions, check if connection is established correctly and the data is received (`$stmt->fetch();` should return true) – zavg Apr 14 '13 at 16:28
  • Tried again with the updated version, `var_dump($result);` now shows `int(5)` and `echo $result;` shows `5`. Wheres the actual data? – Simon Carlson Apr 14 '13 at 16:30
  • 5 is the value of some field (which you specified in updated version of query) of your objekt_t table – zavg Apr 14 '13 at 16:32
  • 1
    Right, I misunderstood how this fetches data. Thank you for the help. – Simon Carlson Apr 14 '13 at 16:33
0

You can do it like this:

$stmt = $mysqli->prepare('SELECT * FROM objekt_t WHERE id = ?');
$stmt->bind_param('i', $id);
$stmt->execute();

$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    // $row is an associative array
}
jithujose
  • 551
  • 3
  • 15