PDO Binding Limit

Question

So I have the following

public function search($table, $column, $term, $limit = 5){
        $command = "SELECT name FROM `$table` WHERE `:col` LIKE :term LIMIT :lim";
        $query = $this->connection->prepare($command);
        $query->execute(array(":term"=>"%{$term}%", ':lim'=>$limit, ':col' => $column));
        print_r($query->fetchAll());
    }

and the return value is

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 ''5'' at line 1

From the duplicate, I changed the execute to this

$command = "SELECT name FROM `$table` WHERE ? LIKE ? LIMIT ?";

...
    $query->bindParam(1, $column,  PDO::PARAM_STR);
            $term = "%{$term}%";
            $query->bindParam(2, $term,  PDO::PARAM_STR);
            $query->bindParam(3, $limit,  PDO::PARAM_INT);
            $query->execute();

And now I am getting this:

Array ( )

Is my limit now 0 or something? What's going on?

Why is your code so similar to this other user: http://stackoverflow.com/q/29723967/3933332 ? How is this possible that you write such similar code? — Rizier123, Apr 19 '15 at 00:17
Maybe it's your *friend* as it most times turn out to be in most cases. But you used the same variable names the exact same query, HOW? — Rizier123, Apr 19 '15 at 00:21
@DerishOrie I guess Rizier is just concerned that you use multiple accounts (yours and the one in question look very similar) — kero, Apr 19 '15 at 00:31
I understand, but I answered his question the first time calmly and he persisted. I felt like I was being interrogated which is rather annoying and inconvenient. Not to mention, I feel like I'm being attacked for using this site's function. That's a rather sad statement to make. — DerishOrie, Apr 19 '15 at 00:35
To make it clear here: 1. We should never accuse someone! So I also don't want/do and didn't do it. It's just very strange from my perspective. 2. *I felt like I was being interrogated* You don't have to feel that way. I was just curious, that's all. 3. *I feel like I'm being attacked for using this site's function.* Don't feel like that! No body wants to attack you! (4. I closed the question now since this question already was answered. If it wouldn't help you the dupe, edit this question with an update and say it, then we can reopen it!) ((@kingkero)) — Rizier123, Apr 19 '15 at 00:43
Intentions don't always produce intentional results, but I am glad we cleared up that this service is not for interrogation but rather for assistance. Anyway, I still don't have the desired result from my function. — DerishOrie, Apr 19 '15 at 00:50
@DerishOrie You can't use numbers in the `bindParam()` call, since you don't use positional placeholders (`?`), you used named placeholders (e.g. `:xy`) so you need to use it like this: `$query->bindParam(':lim', (int) trim($limit), PDO::PARAM_INT);` (And you can't bind column names! You have to use variables for those) — Rizier123, Apr 19 '15 at 00:53
So if I have a tag system where the user can select tags, how would I implement that into the column system so that security is not an issue? And I changed from :limit and such to 1, 2, 3 due to an answer I saw, I forgot to post that, sorry. — DerishOrie, Apr 19 '15 at 00:55
Nvm, I got it. Thanks for the latter part of the assistance track. — DerishOrie, Apr 19 '15 at 00:58

score 1 · Accepted Answer · answered Apr 20 '15 at 13:17

It looks like you're trying to bind a column in your query, which you're not allowed to do.

Prepared statements are awesome for two reasons:

They separate your query from your data values, preventing any possibility of SQL injection
They allow the same query and query execution plan to be used multiple times with many different data, which is much more efficient.

As a query plan is based on information such as which columns are used, what columns have indexes on them which can be used, what tables to retrieve data from, a query plan is specific to which columns are retrieved. For SQL injection protection, it's also necessary to prevent calling of functions, or retrieving columns which may or may not be displayed to the user to prevent data leakage. Both of these points mean that prepared statements with bound parameters cannot provide anything other than data, which columns are not.

This is why you're having issues binding a column - your query isn't throwing an error because you're actually allowed to compare two values which aren't column data.

A somewhat nasty way around this is to include a conditional for every column you want to search:

SELECT * FROM table
WHERE 
    (:column = "colA" and colA like :term) 
    OR (:column = "colB" AND colB like :term) 
    OR ...
;

PDO Binding Limit

1 Answers1