EzDevInfo.com

PDO's query vs execute

Are they both do the same thing, only differently?

Is there any difference besides using prepare between

$query = "SELECT * FROM table";
$sth = $db->query($query);
$result = $sth->fetchAll();

and

$sth = $db->prepare("SELECT * FROM table");
$sth->execute();
$result = $sth->fetchAll();

?

How can I properly use a PDO object for a Select query

I've tried following the PHP.net instructions for doing Select queries but I am not sure the best way to go about doing this.

I would like to use a parameterized Select query, if possible, to return the ID in a table where the name field matches the parameter. This should return one ID because it will be unique.

I would then like to use that ID for an Insert into another table, so I will need to determine if it was successful or not.

I also read that you can prepare the queries for reuse but I wasn't sure how this helps.

How to properly set up a PDO connection

From time to time I see questions regarding connecting to database.
Most answers is not the way I do it, or I might just not get the answers correctly. Anyway; I've never thought about it because the way I do it works for me.

But here's a crazy thought; Maybe I'm doing this all wrong, and if that's the case; I would really like to know how to properly connect to a MySQL database using PHP and PDO and make it easy accesable.

Here's how I'm doing it:

First off, here's my filestructure (stripped down):

public_html/

• index.php

• initialize/
  -- load.initialize.php
  -- configure.php
  -- sessions.php

index.php
At the very top, I have require('initialize/load.initialize.php');.

load.initialize.php

#   site configurations
    require('configure.php');
#   connect to database
    require('root/somewhere/connect.php');  //  this file is placed outside of public_html for better security.
#   include classes
    foreach (glob('assets/classes/*.class.php') as $class_filename){
        include($class_filename);
    }
#   include functions
    foreach (glob('assets/functions/*.func.php') as $func_filename){
        include($func_filename);
    }
#   handle sessions
    require('sessions.php');

I know there's a better, or more correct, way to include classes, but can't remember what it was. Haven't gotten the time to look into it yet, but I think it was something with autoload. something like that...

configure.php
Here I basically just override some php.ini-properties and do some other global configuration for the site

connect.php
I've put the connection onto a class so other classes can extends this one...

class connect_pdo
{
    protected $dbh;

    public function __construct()
    {
        try {
            $db_host = '  ';  //  hostname
            $db_name = '  ';  //  databasename
            $db_user = '  ';  //  username
            $user_pw = '  ';  //  password

            $con = new PDO('mysql:host='.$db_host.'; dbname='.$db_name, $db_user, $user_pw);  
            $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $con->exec("SET CHARACTER SET utf8");  //  return all sql requests as UTF-8  
        }
        catch (PDOException $err) {  
            echo "harmless error message if the connection fails";
            $err->getMessage() . "<br/>";
            file_put_contents('PDOErrors.txt',$err, FILE_APPEND);  // write some details to an error-log outside public_html  
            die();  //  terminate connection
        }
    }

    public function dbh()
    {
        return $this->dbh;
    }
}
#   put database handler into a var for easier access
    $con = new connect_pdo();
    $con = $con->dbh();
//

Here I do believe there's room for massive improvement since I recently started learning OOP, and using PDO instead of mysql.
So I've just followed a couple of beginners tutoraials and tried out different stuff...

sessions.php
Beside handeling regular sessions, I also initialize some classes into a session like this:

if (!isset($_SESSION['sqlQuery'])){
    session_start();
    $_SESSION['sqlQuery'] = new sqlQuery();
}

This way this class is avalible all over the place. This might not be good practice(?)...
Anyway, this is what this approch allows me to do from everywhere:

echo $_SESSION['sqlQuery']->getAreaName('county',9);  // outputs: Aust-Agder (the county name with that id in the database)

Inside my sqlQuery-class, which extends my connect_pdo-class, I have a public function called getAreaName which handles the request to my database.
Pretty neat I think.

Works like a charm
So that's basically how I'm doing it.
Also, whenever I need to fetch something from my DB from not whitin a class, I just do something similar to this:

$id = 123;

$sql = 'SELECT whatever FROM MyTable WHERE id = :id';
$qry = $con->prepare($sql);
$qry -> bindParam(':id', $id, PDO::PARAM_INT);
$qry -> execute();
$get = $qry->fetch(PDO::FETCH_ASSOC);

Sience I put the connection into a variable inside *connect_pdo.php*, I just have refering to it and I'm good to go. It works. I get my expected results...

But regardless of that; I would really appreciate if you guys could tell me if I'm way off here. What I should do instad, areas I could or should change for improvement etc...

I'm eager to learn...

Why shouldn't I use mysql_* functions in PHP?

What are the technical reasons why I shouldn't use mysql_* functions? (e.g. mysql_query(), mysql_connect() or mysql_real_escape_string())?

Why should I use something else even if they work on my site?

What is the difference between bindParam and bindValue?

What is the difference between PDOStatement::bindParam() and PDOStatement::bindValue()?

What is the difference between MySQL, MySQLi and PDO? [closed]

What is the difference between MySQL, MySQLi and PDO?

Which one is the best suited to use with PHP-MySQL?

MySQL check if a table exists without throwing an exception

What is the best way to check if a table exists in MySQL (preferably via PDO in PHP) without throwing an exception. I do not feel like parsing the results of "SHOW TABLES LIKE" et cetera. There must be some sort of boolean query?

PHP PDO: charset, set names?

I had this previously in my normal mysql_* connection:

mysql_set_charset("utf8",$link);
mysql_query("SET NAMES 'UTF8'");

Do I need it for the PDO? And where should I have it?

$connect = new PDO("mysql:host=$host;dbname=$db", $user, $pass, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

How to debug PDO database queries?

Before moving to PDO, I created SQL queries in PHP by concatenating strings. If I got database syntax error, I could just echo the final SQL query string, try it myself on the database, and tweak it until I fixed the error, then put that back into the code.

Prepared PDO statements are faster and better and safer, but one thing bothers me: I never see the final query as it's sent to the database. When I get errors about the syntax in my Apache log or my custom log file (I log errors inside a catch block), I can't see the query that caused them.

Is there a way capture the complete SQL query sent by PDO to the database and log it to a file?

Can PHP PDO Statements accept the table or column name as parameter?

Why can't I pass the table name to a prepared PDO statement?

$stmt = $dbh->prepare('SELECT * FROM :table WHERE 1');
if ($stmt->execute(array(':table' => 'users'))) {
    var_dump($stmt->fetchAll());
}

Is there another safe way to insert a table name into a SQL query? With safe I mean that I don't want to do

$sql = "SELECT * FROM $table WHERE 1"

What are the disadvantages of using persistent connection in PDO

In PDO, a connection can be made persistent using the PDO::ATTR_PERSISTENT attribute. According to the php manual -

Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

The manual also recommends not to use persistent connection while using PDO ODBC driver, because it may hamper the ODBC Connection Pooling process.

So apparently there seems to be no drawbacks of using persistent connection in PDO, except in the last case. However., I would like to know if there is any other disadvantages of using this mechanism, i.e., a situation where this mechanism results in performance degradation or something like that.

Are PDO prepared statements sufficient to prevent SQL injection?

Let's say I have code like this:

$dbh = new PDO("blahblah");

$stmt = $dbh->prepare('SELECT * FROM users where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );

The PDO documentation says:

The parameters to prepared statements don't need to be quoted; the driver handles it for you.

Is that truly all I need to do to avoid SQL injections? Is it really that easy?

You can assume MySQL if it makes a difference. Also, I'm really only curious about the use of prepared statements against SQL injection. In this context, I don't care about XSS or other possible vulnerabilities.

Can I bind an array to an IN() condition?

I'm curious to know if it's possible to bind an array of values to a placeholder using PDO. The use case here is attempting to pass an array of values for use with an IN() condition.
I'm not very good at explaining, so here's some psuedocode to demonstrate. I'd like to be able to do something like this:

<?php
$ids=array(1,2,3,7,8,9);
$db = new PDO(...);
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id IN(:an_array)'
);
$stmt->bindParam('an_array',$ids);
$stmt->execute();
?>

And have PDO bind and quote all the values in the array.

At the moment I'm doing:

<?php
$ids = array(1,2,3,7,8,9);
$db = new PDO(...);
foreach($ids as &$val)
    $val=$db->quote($val); //iterate through array and quote
$in = implode(',',$ids); //create comma separated list
$stmt = $db->prepare(
    'SELECT *
     FROM table
     WHERE id IN('.$in.')'
);
$stmt->execute();
?>

Which certainly does the job, but just wondering if there's a built in solution I'm missing?

Row count with PDO

There are many conflicting statements around, what is the best way to row count using PDO in PHP? Before using PDO I just simply used mysql_num_rows.

fetchAll is something I won't want as I may sometimes be dealing with large datasets, so not good for my use.

Any suggestions?

PDO Prepared Inserts multiple rows in single query

I am currently using this type of SQL on MySQL to insert multiple rows of values in one single query:

INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),...

On the readings on PDO, the use prepared statements should give me a better security than static queries.

I would therefore like to know whether it is possible to generate "inserting multiple rows of values by the use of one query" using prepared statements.

If yes, may I know how can I implement it?