Cannot use PHP var in a SQL Query

Question

I'm trying to show each row where userCZ = $_SESSION['user'], but whenever I try with the var, it doesn't find nothing at all. If I replace the var for a literal string, It works perfectly. I've also tried to check if the var is not empty and it returns the proper value at the begin of the script. I think I am missing something in the syntax but I've tried with '' and "" and still not working.

<?php
  session_start();
  $userCZ=$_SESSION['user'];


require_once __DIR__ . "/../../init.php";
require_once __DIR__ . "/../../functions/db.php";

if (isset($_GET['start']) && isset($_GET['end'])) {
$stmt = 'SELECT * FROM the_events WHERE userCZ = '$userCZ' AND start_date >= 
:start and end_date <= :end';
$_events = QuickPdo::fetchAll($stmt, [
    'start' => $_GET['start'],
    'end' => $_GET['end'],
]);



$events = [];
foreach ($_events as $e) {
    $events[] = [
        'id' => $e['id'],
        'title' => $e['title'],
        'project' => $e['project'],
        'start' => dateMysqlTime2Iso8601($e['start_date']),
        'end' => dateMysqlTime2Iso8601($e['end_date']),
    ];
}
echo json_encode($events);
}

one thing is that you have 'SELECT * FROM the_events WHERE userCZ = '$userCZ' AND start_date >= :start and end_date <= :end', but you need a . to separate the different parts of the strings — Zachary Weixelbaum
– Zachary Weixelbaum, Commented Oct 10, 2017 at 1:25
You should enable error logs, this should be causing an error. You also should bind $usercz. — chris85
– chris85, Commented Oct 10, 2017 at 1:28
echo your $stmt variable and see what it is outputs. Variable values are not substituted in single quotes (') so use $stmt = "SELECT * ....." — blokeish
– blokeish, Commented Oct 10, 2017 at 1:35
Change all those values to placeholder values. You're doing it for :start and :end, so do it for the rest. DO NOT use string interpolation to do this. — tadman
– tadman, Commented Oct 10, 2017 at 2:23
Based on the mistake made here you need to use a syntax highlighting text editor that can instantly show you any mis-steps in formatting your code. This is pretty obvious using even Stack Overflow's own internal formatter. — tadman
– tadman, Commented Oct 10, 2017 at 2:24

tadman · Accepted Answer · 2017-10-10 02:38:54Z

2

This can be addressed by binding all of the dynamic values:

$_events = QuickPdo::fetchAll(,
  'SELECT * FROM the_events WHERE userCZ = :userCZ AND start_date >= :start and end_date <= :end',
  [
    'userCZ' => $userCZ,
    'start' => $_GET['start'],
    'end' => $_GET['end'],
  ]
);

Avoid using string interpolation unless you have no other option, and when that occurs, take every possible precaution to ensure you're doing it safely.

answered Oct 10, 2017 at 2:38

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot use PHP var in a SQL Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related