Asked  7 Months ago    Answers:  5   Viewed   55 times

I am refactoring some old code, including rewriting basic mysql queries to use PDO.

The following works brilliantly in all browsers and for all image types:

$query = 'SELECT image FROM image WHERE imageid=' . $image_id;
$result = mysql_query($query, $db_conn); querycheck($result);
header("Content-type: image");
echo mysql_result($result, 0);

Unfortunately, however I rewrite it using PDO, it doesn't work. I've been through the entire PDO documentation and the standard web search, but none of the advice/solutions work.

How can one easily fetch and image from MySQL using PDO and display it?

Edit 1:

Matthew Ratzloff gives what should be the obvious answer below, but it does not work. Here is the actual code that I test using PDO (and I have tried many variants/parameters):

$connectstring_temp = 'mysql:host=' . $A . ';dbname=' .$B;
$dbh_temp = new PDO($connectstring_temp, $login, $password);
#$dbh_temp->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
#$dbh_temp->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);

$sql = "SELECT image FROM image WHERE imageid=" . $image_id;
$query = $dbh_temp->prepare($sql);
$query->execute();

$query->bindColumn(1, $image, PDO::PARAM_LOB);
$query->fetch(PDO::FETCH_BOUND);
header("Content-Type: image");
echo $image;

I've kept the same syntax, although for the final code $image_id needs to be passed as a parameter. The code above does NOT work. PDO works fine for all other queries of all types.

 Answers

98

You need to paramaterize the imageid value and bind the parameter to PDO::PARAM_LOB:

$sql = "SELECT image FROM image WHERE imageid=:id";
$query = $db_conn->prepare($sql);
$query->execute(array(':id' => $image_id));

$query->bindColumn(1, $image, PDO::PARAM_LOB);
$query->fetch(PDO::FETCH_BOUND);
header("Content-Type: image");
echo $image;

Of course, you'll also want to specify the complete, correct content type (e.g., image/png).

Wednesday, March 31, 2021
 
altermativ
answered 7 Months ago
95

Change the function to this:

function run_db($sqlcom,$exe){
    $conn = new PDO('mysql:host=localhost;dbname=dbname', 'usr', 'pass');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare($sqlcom);
    $stmt->execute($exe);
    return $stmt;
}

and the call to that function to:

try {
    $stmt = run_db('SELECT * FROM posts WHERE status= :published ORDER BY id DESC LIMIT 5',array(':published' => 'published'));   
    while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $contents = $result['content'];
        $title = $result['title'];

EDIT: Better solution is the one that jeroen advices - to return all the fetched objects at once:

function run_db($sqlcom,$exe){
    $conn = new PDO('mysql:host=localhost;dbname=dbname', 'usr', 'pass');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare($sqlcom);
    $stmt->execute($exe);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Then calling this way:

try {
    $data = run_db('SELECT * FROM posts WHERE status= :published ORDER BY id DESC LIMIT 5',array(':published' => 'published'));   
    foreach($data as $result) {
        $contents = $result['content'];
        $title = $result['title'];

EDIT 2: Anyway - wrapping such a logic into one function is not very good idea. now You are limited with executing of only SELECT queries and the resulting array containing always only record's associative array. What if You would like to (for any reason) retrieve the array of objects, or even only one single value? What if You would like to execute INSERT, UPDATE, DELETE queries???

If You for sure want to go this way, then I'd suppose creating a class with functions like this:

class MyPDO {
    private $connection;

    static $instance;

    function __construct() {
        $this->connection = new PDO('mysql:host=localhost;dbname=dbname', 'usr', 'pass');
        $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    static function getInstance() {
        return self::$instance ?  : self::$instance = new MyPDO;
    }

    // retrieves array of associative arrays
    function getAssoc($sqlcom, $exe) {
        $stmt = $this->connection->prepare($sqlcom);
        $stmt->execute($exe);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // retrieves array of objects
    function getObj($sqlcom, $exe) {
        $stmt = $conn->prepare($sqlcom);
        $stmt->execute($exe);

        return $stmt->fetchAll(PDO::FETCH_OBJ);
    }

    // retireves one single value, like for SELECT 1 FROM table WHERE column = true
    function getOne($sqlcom, $exe) {
        $stmt = $conn->prepare($sqlcom);
        $stmt->execute($exe);

        return $stmt->fetchColumn();
    }

    // just executes the query, for INSERT, UPDATE, DELETE, CREATE ...
    function exec($sqlcom, $exe){
        $stmt = $conn->prepare($sqlcom);

        return $stmt->execute($exe);
    }
}

Then You can call it this way:

try {
    $pdo = MyPDO::getInstance();
    foreach($pdo->getAssoc('MySQL QUERY'), array($param, $param)) as $result) {
        print_r($result);
    }
} catch(Exception $e) {
    // ...
}
Friday, May 28, 2021
 
Trott
answered 5 Months ago
89

SQL Injection is only a problem where user-entered data needs to be sent to your database. A query like SELECT * FROM Player includes no user data. It's entirely safe from SQL Injection for that reason.

In fact, in this case, there's no benefit in using a prepared statement. A prepared statement here will make two calls to the database when one will do.

You can do this:

$getPlayers = $db->query("SELECT * FROM Player");
foreach ($getPlayers as $player) {
  // do something
}

No preparation, no binding, and just one call to the database.

Saturday, May 29, 2021
 
PHPWDev
answered 5 Months ago
35

You won't be able to escape the table name (I hope that $tablename isn't coming from an outside source - If it is, you will need to whitelist what table names are allowed). In PDO, your code could look something like:

$allowedTables = array('posts', 'users');
if(!in_array($tablename, $allowedTables)){
    throw new Exception('Invalid table name: ' . $tablename);
}

$keyword = 'something';
$stmt = $dbh->prepare("SELECT * FROM " . $tablename . " WHERE keyword = :keyword");
$stmt->bindParam(':keyword', $keyword);
$stmt->execute();
Saturday, May 29, 2021
 
Palladium
answered 5 Months ago
67

I think you are looking for:

while($row = $stmt->fetch(/* PDO::FETCH_ASSOC */)) {
    // do loop stuff
}

PDO::fetchAll() returns an associative array of all of the query results (a 2-D array). This is not recommended for large result sets according to the PHP docs. PDO::fetch() returns just one row from a result set and mimics mysql_fetch_array(). See http://php.net/manual/en/function.mysql-fetch-array.php for more details.

Monday, October 18, 2021
 
Carter Pape
answered 3 Days ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :