[RESOLVED] Trying to make stuff more secure. Have some Questions on prepared statements.

[minifairy]

ok currently I have a query set up to pull one row from a table like this. querymeres is a function that does a mysqli query and returns the result.

$query2 = "select * from gallery where id='".$id."'";
    $result2 = querymeres($query2);
$row2 =$result2->fetch_object();

so at this point assuming I have a result that is an object I echo $row->name, $row->id etc to output the contents of the row.

If I understand correctly converting this to a prepared statement would go something like this:

#prepare statement
	if ($query2 = $mysqli->prepare("select * from gallery where id=?")) {
	 /* bind parameters for markers */
        $query2->bind_param("s", $id);
     /* execute query */
        $query2->execute();

And that is sort of where I get lost.

So this means I need to enable execute permissions on the db right?
How do I get an object that I can access with $row->item or is that not possible? And if not how do I go about getting the info out of my row? Do I have to address every single column that I want by name and assign it to something cryptic?
Also How is this anymore secure than the current way? I basically don't see what it is doing and the documentation isn't very clear to me at least.

Thanks in advance.

[pbismad]

And that is sort of where I get lost.

That's because the php mysqli extension lost their way at that point too (actually, it got lost at the bind_param() statement with a variable number of parameters that doesn't work well when dynamically building queries.) If your php version was built to use the mysqlnd driver (which may not be under your control), you can convert the prepared query result into a traditional mysqli result ( see mysqli_stmt::get_result() ) and use mysqli fetch statements on it. If not, you must either use bind_result() and specifically bind a variable to each selected column OR you can access the query metadata and dynamically bind the variables (requires a lot of code, where you would have to extend the mysqli class with a general purpose method to avoid repeating it for every query that returns a result set.)

The php PDO extension is more constant and easier to use. If you can, switch to it.

Edit: Also, for either the msyqli or PDO extension, use exceptions to handle errors. This will eliminate the need to have conditional logic in your code around every statement that can fail. Your main code will only need to deal with error free database statements.

This is the PDO equivalent of your example code (requires a database connection using the pdo extension in $pdo)-

$query = "SELECT * FROM gallery WHERE id= ?";
$stmt = $pdo->prepare($query);
$stmt->execute(array($id));
$row = $stmt->fetch(PDO::FETCH_OBJ);

[NogDog]

As far as why to use prepared statements with bound parameters, it's mainly to ensure that input parameters are properly escaped in order to prevent SQL injection. Without them, you would need to do something like this:

$query = "SELECT * FROM some_table WHERE id = " intval($_POST['id']);

...or...

$query = "SELECT * FROM some_table WHERE user_name = '" . $mysqli->escape_string($_POST['username']) . "'";

PS: Another reason I prefer PDO is that you can use named place-holders:

$sql = "SELECT * FROM some_table WHERE first_name = :first_name AND last_name = :last_name";
$stmt = $pdo->prepare($sql);
$stmt->execute(array(
    ':first_name' => $_POST['first_name'],
    ':last_name' => $_POST['last_name']
));

[NogDog]

Oh, by the way, I'm guessing that the id column is an integer? If so, then you would want to use an "i" instead of "s" in the first argument to the mysqli bind_param() call.

[minifairy]

Ok so if I make sure to excape everything or make sure I'm getting what I expect I shouldn't need to use the prepare stmt bit? I think I would rather do the coding to make sure I'm not getting what I don't want. Most of my user input is expected to be in a pretty small range. Thanks for all the input. I can work with this. :-)

[NogDog]

The important thing is to assume the worst: at some point some user will submit something that would break things one way or another if not properly sanitized. If you trust yourself to find every such place that could possibly happen and properly sanitize the inputs, then you'll be fine (assuming your trust is properly placed 😉 ). On the other hand, if you always use prepared statements with bound parameters for all variable inputs, the system takes care of it for you, and it's one less place you might leave a security hole.