Prepared statement with PHP and MSSQL

Question

I have an odd scenario about pdos. With prepared statements I get 0 results from database. But hardcoded I get normal results. This is a sql query for mssql (< 2012) to get limited results.

Prepared Statement (just do not wonder about the top and offset variable. I'm setting those in the function just for testing purpose. Also $conn is edited for stackoverflow. The prepare function is reachable from the function, so there is no problem):

public function myFunction($top, $offset) {
    try {
        $top = 20;
        $offset = 1;

        $sql = "SELECT TOP :top * FROM (
            SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS t1
            FROM myTable) AS nU WHERE t1 >= :offset";

        $statement = $conn->prepare($sql);
        $statement->execute(array(':top' => $top, ':offset' => $offset));

        return $statement->fetchAll();

    } catch (Exception $e) {
        echo $e->getMessage();
    }
}

Result is an array with 0 elements.

But with this it works perfectly:

public function myFunction($top, $offset) {
    try {
        $top = 20;
        $offset = 1;

        $sql = "SELECT TOP 20 * FROM (
            SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS t1
            FROM myTable) AS nU WHERE t1 >= 1";

        $statement = $conn->prepare($sql);
        $statement->execute();

        return $statement->fetchAll();

    } catch (Exception $e) {
        echo $e->getMessage();
    }
}

With this I get results correctly.

How this is possible? What's wrong with the prepared statement? I have a lot of prepared statements and it worked fine before.

Thanks for answers.

@EDIT - updated code - still not working:

public function myFunction($top, $offset) {
    try {
        $top = 20;
        $offset = 1;

        $sql = "SELECT TOP :top * FROM (
            SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS t1
            FROM myTable) AS nU WHERE t1 >= :offset";

        $statement = $conn->prepare($sql);

        $statement->bindParam(':top', $top, PDO::PARAM_INT);
        $statement->bindParam(':offset', $offset, PDO::PARAM_INT);

        $statement->execute();
        return $statement->fetchAll();
    } catch (Exception $e) {
        echo $e->getMessage();
    }
}

I think `TOP` expects an int and you are passing it a string (with the prepared statement). `var_dump($statement->execute());` prolly outputs false — bassxzero, Aug 13 '18 at 15:20
[This link](https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause) might be helpful. — aynber, Aug 13 '18 at 15:22
@bassxzero: yeah it outputs false. But $statement->bindValue(':top', (int) $top, PDO::PARAM_INT); $statement->bindValue(':offset', (int) $offset, PDO::PARAM_INT); doesn't work either — arkert, Aug 13 '18 at 15:41
would replacing `:top` with `'.$top.'` not help here? or am i being very stupid — Isaac, Aug 13 '18 at 16:18
Yeah, then it works. But I want to use prepared statements with bounding parameters for security reason (sql injection). Why I can't do that with bindValue? Really cannot see the reason for this... — arkert, Aug 13 '18 at 16:24
Try enabling the `PDO::ATTR_EMULATES_PREPARES` option in PDO. — Barmar, Aug 13 '18 at 16:27
@Isaac: Directly after prepare and before execute...: (...) $statement = $conn->prepare($sql); $statement->bindValue(':top', $top, PDO::PARAM_INT); $statement->bindValue(':offset', $offset, PDO::PARAM_INT); $statement->execute(); (...) — arkert, Aug 13 '18 at 16:42
Try changing BindValue() to BindParam() and see if you get any change in anything there — Isaac, Aug 13 '18 at 16:49
Can you also update the code so it shows everything like BindValue() etc? — Isaac, Aug 13 '18 at 16:55
Try to change fetchAll to `fetch(PDO::FETCH_ASSOC);` also I think you have a typo with `AS nU` forgive me if I'm wrong but my implementation of this in the past is `as num FROM .... ` — Isaac, Aug 13 '18 at 17:06
I need fetchAll because I need all elements and not only one. (It's a list) The part with "AS nU" is only an alias for the query. I named it "nU" for "not used". The query is never used so I just gave it a name. Just for clarification, like I already said in my question, it works with numbers (without bindParam/bindValue). It also works if I use $top and $offset in prepare statement. The problem is the binding — arkert, Aug 13 '18 at 17:13

score 0 · Accepted Answer · answered Aug 13 '18 at 17:52

0

It's not allowed to use parameter bindings in PDO for SELECT and FROM part of a sql query.

I replaced the whole query with another one where I don't have to set TOP

answered Aug 13 '18 at 17:52

arkert

52
10

Prepared statement with PHP and MSSQL

1 Answers1