2

I failed to find out by browing google. So i decided to ask here. Is parameterezed query supported i postgre using php pdo?

If not. How can i reach the same safety?

Note that safety, thus injection prove is an absolute must. Using another database is not an option as a sidenote.

In a response to Fonini;

Parameterized queries like so:

     $st = $db->prepare(
         "insert into vendors set
         first_name = :first_name,
         last_name = :last_name"
     );
     $st->execute(array(
         ':first_name' => $vendor->first_name,
         ':last_name' => $vendor->last_name
     ));

not using bindParam

This doesn't insert for me.

My code looks like so:

$project = new Project();
$project->id = 'sequence string';
$project->projectName = 'A project name';

$project->saveProject();

and in the project model:

public function saveProject() {
    $this->_db->query("INSERT INTO projects VALUES (:id,:projectName)", array(':id' => $this->_projectFields['id'], ':projectName' => $this->_projectFields['projectName']))->save();

}

the query method in my db class puts the query in the private $_query field, and the aray parameter in the private $_parameters field. I then call the save method, which prepares the statement, giving $this->_query as parameter to PDO::prepare(), and then call PDO::execute giving $this->_parameters as parameter. This fails to insert

Joey Roosing
  • 2,145
  • 5
  • 25
  • 42
  • 1
    Why wouldn't it? I don't know PDO, but PostgreSQL supports parameterized queries in JDBC and ODBC. – Paul Tomblin Nov 04 '11 at 15:54
  • Because if i use unparamererized queries, it inserts data, and uaing parameterized, it doesnt. Using exact the same data. I validated that with print r's and echos. – Joey Roosing Nov 04 '11 at 16:04
  • 1
    Show your code. The problem is on your end, not PostgreSQL's. – Paul Tomblin Nov 04 '11 at 16:23
  • Why don't you use pg_query_params() ? PDO is just overhead when you're just using PostgreSQL. Just use the PostgreSQL-functions in PHP – Frank Heikens Nov 04 '11 at 17:19
  • I don't know about the standard functions build in php for postgre, but we always use PDO for projects. It is the standard. Projects also tend to grow quite large, communicate with other platforms such as android etc. – Joey Roosing Nov 04 '11 at 18:21
  • As a general rule, if you can't get a language or dbms feature working and it's a function that literally thousands of people have used and would have noticed if it wasn't working, blame your code first, and not the language feature. – Paul Tomblin Nov 05 '11 at 13:37

2 Answers2

5

Yes, parameterized queries work perfectly with PHP PDO.

$db = new PDO('pgsql:host=localhost;dbname=cars', 'user', 'password');

$cars = $db->prepare('SELECT * FROM cars WHERE id = :id');
$cars->bindParam(':id', $id = 20, PDO::PARAM_INT);
$cars->execute();

$result = $cars->fetch();
echo 'Name: ' . $result['name'];
tomasz
  • 403
  • 3
  • 16
fonini
  • 3,243
  • 5
  • 30
  • 53
  • Do you get errors or just does not inserts data? The save() method does not exists. – fonini Nov 04 '11 at 17:26
  • The save method exists in my database class. I created it. I first call query method in my dbhelper class, which sets some variables, then save(), to save that data. I don't get errors, the application flow works as expected. It just doesn't insert the data. If you read my editted post I tihnk and hope it will become more clear. – Joey Roosing Nov 04 '11 at 18:15
  • Have you tried to add this parameter to your PDO object? $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); – fonini Nov 04 '11 at 18:18
  • 1
    No. I have not. I will do this when I get back at work on monday. Thankks for pointing it out. Hope it will help. I can't test anything anymore right now. Are you convinced it is possible? Without the bindParam method? – Joey Roosing Nov 04 '11 at 18:22
  • I called a colleague to give me access to the work server. There were errors generated form the query. Thanks a ton. – Joey Roosing Nov 04 '11 at 18:38
2

There is several ways how to hand in values instead of building a SQL statement with the text representation of the values.

Prepared statements are most likely what you are thinking of.

You can also create functions ("stored procedures") and hand in values as parameters.

Here is a related answer to a related question

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Nice find, and thanks for the links. I get your idea, but prefferably, I use my editted question code. +1 for the links though – Joey Roosing Nov 04 '11 at 17:20