mysql - PHP Limit with session var sql error

538

My questions is get this error:

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

This piece of code gives me the error:

public function toonAlles()
{
    try {
        if (isset($_SESSION["limiet"])) {
            $limitwaarde = $_SESSION["limiet"];
            echo $limitwaarde;
            var_dump($limitwaarde);
            $sql = "SELECT * FROM lottogamedata LIMIT :limietwaarde";
            $smt = $this->db->prepare($sql);
            $smt->bindParam("limietwaarde", $limitwaarde, PDO::PARAM_INT);

        }
        else{
            $sql = "SELECT * FROM lottogamedata";
            $smt = $this->db->prepare($sql);
        }

        $smt->execute();
        $lottotabel = $smt->fetchAll(PDO::FETCH_OBJ);

    } catch (PDOException $e) {
        die($e->getMessage());
    }

    return $lottotabel;
}

this is where initialize the session:

session_start();
$Winkel = Winkel::getEuroInstantie();
Uitvoer::toonKeuzeMenu();
Uitvoer::extraFunctions();
if (isset($_POST["submitTheSpecifyRequest"])){
    helper::voegToegAanDeSessie($_POST["maxNumbers"]);

}

this is the submitTheSpecifyRequest button:

public static function extraFunctions(){
  ?>
   <form method="post" id="extraOpties">
     <label for="maxNumbers">Limit the results by</label>
     <input name="maxNumbers" id="maxNumbers" type="number" required min="1" value="1">
     <input type="submit" value="Specify your search" name="submitTheSpecifyRequest" id="submitTheSpecifyRequest"/>
   </form>
  <?php
}

Thanks in advance for me helping me out!

160

Answer

Solution:

PDO'sbindParam has a bug, where if you try to bind a string withPDO::PARAM_INT it will still actually bind it as a string anyway (with quotes). You need to convert your value to an int yourself:

Note that you cannot cast it to an int in the call tobindParam itself, because it expects a reference. By casting inline in the function call you won't have a variable it can reference, so you must cast it to a variable beforehand.

// won't work:
$limitwaarde = $_SESSION["limiet"];
$smt->bindParam("limietwaarde", (int)$limitwaarde, PDO::PARAM_INT);

// will work:
$limitwaarde = (int)$_SESSION["limiet"];
$smt->bindParam("limietwaarde", $limitwaarde, PDO::PARAM_INT);
783

Answer

Solution:

It seems that $limitwaarde is not an int, session stored variables are maybe stored as strings

try :

$smt->bindParam(":limietwaarde", intval($limitwaarde), PDO::PARAM_INT);

People are also looking for solutions to the problem: contructing API with PHP JSON

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.