PDO bindParam/Value not working

Question

I am trying to get a database query page to work but cant seem to do so.

my Code so far (here I tried bindValue, but previously tried bindParam and got the same result):

    var_dump($_POST);


    $dbh = new PDO ("mysql:host=$myServer;dbname=$myDB", $myUser, $myPw);
    $columName = $_POST["columName"];
    $tblName = $_POST["tblName"];
    $valueName = $_POST["valueName"];
    $specificValue = $_POST["specificValue"];

    $stmt = $dbh->prepare("SELECT :columName FROM :tblName Where :valueName = :specificValue");
    $stmt->bindValue(":columName", $columName);
    $stmt->bindValue(":valueName", $valueName);
    $stmt->bindValue(":tblName", $tblName);
    $stmt->bindValue(":specificValue", $specificValue);
    $stmt->execute();

    $result = $stmt->fetch();

    if(empty($result)){echo "empty";}
    print_r ($stmt);
    print_r($result);

Printing result and $stmt brings following results:

empty PDOStatement Object ( [queryString] => SELECT :columName FROM :tblName Where :valueName = :specificValue )

What did I do wrong? What could I try to get it to work? I am new to the whole coding thing, so please ask if I forgot any code or other important information! Thanks!

Adding on what @Fred-ii- said - you can only bind **values** - hence the `bindValue` function name. There is no way you can bind table or column names. When you think about it, it's pointless to do so. — N.B., May 08 '14 at 15:21
@Fred-ii- is there another way to do this? How can I design a query with input fields getting the name of the column and table? — user3568224, May 08 '14 at 15:21
When in development, add `$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after the connection is opened. — Funk Forty Niner, May 08 '14 at 15:21
You can use variables `SELECT $var FROM $table_var` but you can't bind those anyway if that's your intention. — Funk Forty Niner, May 08 '14 at 15:22
@N.B. yeah Im new so I dont know all this stuff but it makes sense... Any advice on resources to learn? How can I design a form to specify which columns/tables should be used? — user3568224, May 08 '14 at 15:22
@Fred-ii- What do you mean I cant bind those? Thanks I will remember that for the future! — user3568224, May 08 '14 at 15:23
http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991422 — Your Common Sense, May 08 '14 at 15:23
You can bind values for anything other than a SELECT and FROM. Don't try and re-invent the wheel. — Funk Forty Niner, May 08 '14 at 15:24
So I would have to let the user first chose table and columns he wants to work with and then I could design a suitable query? any thoughts on how to best do that? — user3568224, May 08 '14 at 15:24
Yes. You can take a user's input, save/assign it to a POST variable then do as I mentioned above. — Funk Forty Niner, May 08 '14 at 15:25
so if I would directly go: $stmt = $dbh->prepare("SELECT $columName FROM $tableName Where :valueName = :specificValue"); it would have worked? — user3568224, May 08 '14 at 15:28
It wouldn't. What gets bound via bindValue or bindParam gets cleaned and quoted (unless integer). That means you receive single-quote marks around those values. That turns your queries into this: `SELECT column FROM table WHERE 'someColumn' = 'Some Value';` - and MySQL will complain about bad syntax here. — N.B., May 08 '14 at 15:34
So I would have to determine valueName too? So the user would only be able to search in e.g. firstNames and would not be able to chose between firstName and lastName (with a second query it would work right?). so not really possible to design queries even a bit more dynamic without opening pandoras box — user3568224, May 08 '14 at 15:45

score 3 · Accepted Answer · answered May 08 '14 at 15:23

3

Placeholder parameters can only represent VALUES in the query. Tables, field names, sql key words, etc.. are all impossible to use placeholders on.

If you need to build a dynamic query and replace field/table names, then you'll have to use good old string construction methods, and be aware that you'll be opening yourself to SQL injection attacks again:

$sql = "SELECT * FROM $foo WHERE $bar = :baz";
$stmt = $dbh->prepare($sql);
$stmt->bindValue(':baz', $baz);

answered May 08 '14 at 15:23

Marc B

356,200
43
426
500

Thanks! Is there any way to design a dynamic query without opening yourself up? – user3568224 May 08 '14 at 15:27
sure, manually escape all of the $vars you insert into the query string. placeholders are wonderful for replacing values in a query. utterly useless for anything else. but generally speaking, building a query that's THAT dynamic is usually a sign of an incorrect design. – Marc B May 08 '14 at 15:29
What would be your recommendation for a more fitting design? – user3568224 May 08 '14 at 15:32
Is this for trying to build a generic "execute any query you want" page? If that's the case, then I guess you do have to build these highly-dynamic queries. – Marc B May 08 '14 at 15:33
1

@user3568224 - Don't have a public generic `getDatabaseValue()` function. Have many specific `getUser()`, `getRole()`, `getProduct()` functions with hard-coded SQL (which can be dynamic or even generated by a common private function, but never built with raw user input). – Álvaro González May 08 '14 at 15:34
I wanted to create a page where the user could enter where and what hes searching for and be able to manipulate the database through a different form. Here I should say that i am doing this for learning purposes only! so any advice and further study resources are much appreciated! So many "small" coded queries with determined tables and columns where only the valueName and specificValue in my example would be done by the user? – user3568224 May 08 '14 at 15:38

score 1 · Answer 2 · answered May 08 '14 at 15:27

1

I'm afraid you need to rethink how parameterised queries work. It's not just a case of magically inserting data in a safe way. It's about distingushing between the structure of a query and the data.

So the database name, the column names, the table names and any SQL keywords are part of the structure of the query. Every time you run the query, they will be the same.

The data, however, can change between running the query.

So the structure needs to be in place when the query is prepared. However, you obviously can't just plonk the $columName variable etc into the query for SQL injection reasons. If you really need to have flexible queries like this (nb that you probably don't) you need to create a whitelist of allowed values, either in your code or retrieved from the database.

answered May 08 '14 at 15:27

lonesomeday

233,373
50
316
318

Thanks! Learned something more today already! What would be a different option to achieve the same goal? – user3568224 May 08 '14 at 15:30
@user3568224 Well, I'd look at avoiding using overly general SQL queries. Think about your database in terms of operations, e.g. "get messages", "create user", etc. Each operation should have a SQL query. Don't abstract too far. It is possible to do dynamic SQL creation, but it's complicated and almost certainly not worth the effort. – lonesomeday May 08 '14 at 15:35
Thanks! Do you know any resources to learn well or shall I just keep posting questions to stackoverflow? its just amazing how fast and good the answers come here! – user3568224 May 08 '14 at 15:40

Álvaro González · Answer 3 · 2014-05-08T15:28:26.883

0

Your query is invalid (you're using parameters for object identifiers) but you are not getting any notification because you have neither configured PDO to throw exceptions nor are calling the error check functions manually.

Add the PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION option to PDO's constructor:

$dbh = new PDO ("mysql:host=$myServer;dbname=$myDB", $myUser, $myPw, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

Once you do so, you'll get a prompt exception on the exact issue, e.g.:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''user' Where 'login' = 'john'' at line 1 in [...]

As you can see, this is trying to run a query like (e.g.):

SELECT 'user_id'
FROM 'user'
Where 'login' = 'john'

Additionally, beware of SQL injection. It's terribly unsafe to compose SQL queries using data from $_POST.

edited May 08 '14 at 15:28

answered May 08 '14 at 15:23

Álvaro González

142,137
41
261
360

Thanks! already changed my code so now ill be getting an error output. Why exactly is it so dangerous to use $_POST with prepared statements? How could I get user input data in differently? – user3568224 May 08 '14 at 15:34
It's dangerous to use `$_POST` to **compose** SQL code (specify tables, columns...). You've giving users the power to freely query your database. – Álvaro González May 08 '14 at 15:36
but by using PDO he is no longer able to change the structure of the query? With the query in my code above I tried to give someone "administrator" right to be able to search the whole database for whatever he wants. But I didnt open up any possibilities for sql injections did i? – user3568224 May 08 '14 at 15:42
Can you highlight what in your code prevents me from typing `ultra_secret_password` in `$_POST["columName"]`? (The only thing that keeps your data safe so far is that your current code crashes.) – Álvaro González May 08 '14 at 15:58
Nothing I know, but it wasnt supposed to... This query was intened for someone like "the god of the database" and not for the everyday user... So i´ve no problems with him getting my password. For the average user I would have done them differently – user3568224 May 08 '14 at 16:02
Fair enough. Chefs use knives everyday. – Álvaro González May 08 '14 at 16:05

PDO bindParam/Value not working

3 Answers3