Page 2 of 4
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 6:44 am
by Celauran
Simple prepared statement example:
MySQLi:
Code: Select all
$sql = new mysqli('host', 'user', 'pass', 'db');
$query = "SELECT name, price, color FROM products WHERE id = ? AND size = ?";
$stmt = $sql->prepare($query);
$stmt->bind_param('is', $_GET['id'], $_GET['size']);
$stmt->execute();
$stmt->bind_result($name, $price, $color);
while ($stmt->fetch()) {
echo "Name: {$name}<br>";
echo "Price: {$price}<br>";
echo "Color: {$color}<br>";
}
PDO:
Code: Select all
$pdo = new PDO('mysql:host=localhost;dbname=whatever', 'user', 'pass');
$query = "SELECT name, price, color FROM products WHERE id = :id AND size = :size";
$stmt = $pdo->prepare($query);
$stmt->execute(array(':id' => $_GET['id'], ':size' => $_GET['size']));
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
echo "Name: {$row->name}<br>";
echo "Price: {$row->price}<br>";
echo "Color: {$row->color}<br>";
}
While in this relatively simple example they aren't all that different, you can see a few places where PDO makes life a little easier and keeps your code looking a little more sane:
- Being able to use placeholders (:id) rather than ? helps keep things readable
- Being able to pass in an array of these placeholders with their values mapped is infinitely more readable than MySQLi's bind_param()
- Not having to bind every single column of your result set keeps code significantly shorter for more complex queries.
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 6:53 am
by simonmlewis
Is it ok to just use:
echo "Name: $row->name";
?
Because that is how ALL our code is done. Having to redo that lot as well is a serious nightmare.
The rest can be added at the tops of code, so is less of a task.
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 6:56 am
by simonmlewis
Code: Select all
$stmt = $pdo->prepare($query);
$stmt->execute(array(':id' => $_GET['id'], ':size' => $_GET['size']));
Surely we don't have to use this? Because what if we are extracting say 40 fields?
Do we have to set each field like this?? Or is there some kind of wildcard "all" one can do?
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 6:58 am
by Celauran
Those are your WHERE inputs, not the columns you're returning. That was also my point in suggesting PDO over MySQLi; if you're using prepared statements, and you really ought to be, then you DO have to bind all selected columns to a variable with MySQLi but you do NOT have to with PDO.
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 7:03 am
by simonmlewis
But what about if you aren't posting anything through - just running a query.
Then isn't the '_GET' redundant? Or does that _GET from the database??
And can it be done without the { } ... as I explained....?
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 7:08 am
by Celauran
Sure, if you aren't binding any parameters, then you aren't binding any parameters. This works pretty much the same everywhere.
Code: Select all
$query = "SELECT foo FROM bar WHERE baz = 12";
$result = $pdo->query($query);
will work the same.
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 7:17 am
by simonmlewis
Code: Select all
$pdo = new PDO('mysql:host=localhost;dbname=dbname', 'user', 'pass');
$query = "SELECT email FROM users WHERE type = 'user'";
$result = $pdo->query($query);
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
echo "Name: $row->email<br>";
}
This isn't bringing up anything - I suspect because of the WHILE line, as I haven't mentioned stmt anywhere else.
I guess that is only when you 'bind'. But I'm not here - so where I am going wrong with this bit?
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 7:25 am
by Celauran
Yes, you're right. PDO::query returns a PDOStatement object, which you've stored in $result. $stmt is not defined. Iterate over $result instead.
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 7:28 am
by simonmlewis
Code: Select all
$pdo = new PDO('mysql:host=localhost;dbname=dbname', 'user', 'pass');
$query = "SELECT email FROM users WHERE type = 'user'";
$result = $pdo->query($query);
while ($row = mysql_fetch_object($query))
{
echo "$row->email<br/>";
}
This correct? I don't think it is ..............
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 7:31 am
by simonmlewis
Nor is this...
Code: Select all
$pdo = new PDO('mysql:host=localhost;dbname=visualsh_justbb', 'justbbguns79', 'asdgf0uUeQc345');
$sth = $dbh->query = ("SELECT email FROM admin WHERE type = 'admin'");
while ($row = $sth->fetch())
{
echo "Name: $row->email<br>";
}
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 7:42 am
by Celauran
No. You're not using mysql_ anymore. That's the whole point. Iterate over $result.
Code: Select all
$query = "SELECT email FROM users WHERE type = 'user'";
$result = $pdo->query($query);
while ($row = $result->fetch(PDO::FETCH_OBJ)) {
echo "$row->email<br/>";
}
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 8:00 am
by simonmlewis
Code: Select all
$pdo = new PDO('mysql:host=localhost;dbname=databasename', 'user', 'pass');
$query = "SELECT email FROM admin WHERE type = 'admin'";
$result = $pdo->query($query);
while ($row = $result->fetch(PDO::FETCH_OBJ)) {
echo "$row->email<br/>";
}
Pretty basic, but this is working a treat.
So this is for extracting data - to INSERT or UPDATE, is the topend query the same as MySQL:
mysql_query("asdfafsd");
Or is the first part totally different? I assume it is, as it's should be without the mysql_query stuff as that is "old".
I've found this:
Code: Select all
$sql = "INSERT INTO books (title,author) VALUES (:title,:author)";
$q = $conn->prepare($sql);
$q->execute(array(':author'=>$author,
':title'=>$title));
and this
Code: Select all
$sql = "UPDATE books
SET title=?, author=?
WHERE id=?";
$q = $conn->prepare($sql);
$q->execute(array($title,$author,$id));
So you set the database UPDATE query, and further on you tell it what variables to use - so you don't put the variable in the top query?
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 8:28 am
by Celauran
Both will work, though I prefer using named placeholders to question marks. I find it makes your code easier to read. In either case, though, you're using the placeholders in place of the variables you'll be passing to the query. The prepared statement handles escaping for you, so you don't need to worry about mysql_real_escape_string or anything of the sort. Set the placeholders in the query, pass them in as arguments when you execute the query.
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 8:33 am
by simonmlewis
Cool.
I just tried the PDO connection style on one of our web site. We use an include fiile, so if we need to alter the login, we do it just once.
The whole site is mysql_extension (I'm talking with hosts about the need for all this). But with the PDO db conn script, the rest of the site collapsed.
Do I have to have the PDO conn script, AND the queries all in PDO to use that as the conn?
Is it all or nothing??
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 8:38 am
by Celauran
Sort of. Any query using mysql_query will need an established mysql_connect. Any query using PDO will need a PDO object. While you're in the process of refactoring, why not use both? Leave the mysql_connect in there so your existing queries continue working and create a PDO object at the same time, say $pdo. As you rewrite each query, you can update it to use PDO. Work through it file by file as time permits. Once you have no more instances of mysql_query remaining, simply remove the mysql_connect call.