SQLSTATE[42S22]: Column not found in REST Api

Question

I can display all results but when I tweaked the code to try to query more specifically, it returns 42s22 {"error": {"text": SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Plant' in 'where clause'} even though there a column with plant value, it shows this error.

<?php

require '.././libs/Slim/Slim.php';
require '.././include/db.php';

\Slim\Slim::registerAutoloader();

$app = new \Slim\Slim();


$app->get('/location/all', function(){
$sql = "SELECT * FROM plant_location";


try{

    $db = new db();

    $db = $db->connect();

    $stmt = $db->query($sql);
    $customers = $stmt->fetchAll(PDO::FETCH_OBJ);
    $db = null;
    echo json_encode($customers);
} catch(PDOException $e){
    echo '{"error": {"text": '.$e->getMessage().'}';
}
});

$app->get('/location/plant/:plant', function($plant){

$sql = "SELECT * FROM plant_location WHERE plant=$plant";



try{

    $db = new db();
    $db = $db->connect();

    $stmt = $db->query($sql);
    $customers = $stmt->fetchAll(PDO::FETCH_OBJ);
    $db = null;
    echo json_encode($customers);
} catch(PDOException $e){
    echo '{"error": {"text": '.$e->getMessage().'}';
}

});
$app->run();
?>

SQL: I was trying to filter results using the plant

CREATE TABLE `plant_location` (
  `id` int(11) NOT NULL,
  `plant` varchar(255) NOT NULL,
  `latitude` varchar(255) NOT NULL,
  `longitude` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `plant_location` (`id`, `plant`, `latitude`, `longitude`, `image`) VALUES
(1, 'Plant', '14.396033', '121.0452128', '//'),
(2, 'Plant B', '14.3967839', '121.0444142', ''),
(3, 'Bawang', '14.3975636', '121.0447081', ''),
(4, 'Bawang', '14.395036', '121.044177', '');

For example if I used /location/plant/Bawang I want to retrieve the entry with id 3 and 4

(3, 'Bawang', '14.3975636', '121.0447081', ''), (4, 'Bawang', '14.395036', '121.044177', '');

You are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). You need to use prepared statements, rather than concatenating variables into your query. Simply escaping your variables is not enough. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). — elixenide, Jul 17 '18 at 13:06
So, when you browse to something like `/location/plant/Bawang` you get that error? — Hackerman, Jul 17 '18 at 13:06
@EdCottrell Thank you! Just figured it out. Posted my solution below, just in case someone will encounter same problem — jtygg, Jul 17 '18 at 13:24

score 0 · Answer 1 · answered Jul 17 '18 at 13:20

Thanks to Ed Cottrell, I found out the answer. I'm posting it here just in case someone will also need the answer. Sharing is caring, I guess

<?php

require '.././libs/Slim/Slim.php';
require '.././include/db.php';

\Slim\Slim::registerAutoloader();

$app = new \Slim\Slim();


$app->get('/location/all', function(){
    $sql = "SELECT * FROM plant_location";


    try{

    // Get DB Object
        $db = new db();
    // Connect
        $db = $db->connect();

        $stmt = $db->query($sql);
        $customers = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($customers);
    } catch(PDOException $e){
        echo '{"error": {"text": '.$e->getMessage().'}';
    }
});

    $app->get('/location/plant/:plant', function($plant){


  //  echo 'here';
    try{
    //    echo 'here';
        // Get DB Object
        $db = new db();

        $db = $db->connect();
        $stmt = $db->prepare('SELECT * FROM plant_location WHERE plant = :plant');
        $stmt->bindValue(':plant', $plant);
        $stmt->execute();
        $customers = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($customers);
    } catch(PDOException $e){
        echo '{"error": {"text": '.$e->getMessage().'}';
    }

});
$app->run();
?>

db

<?php
    class db{
        // Properties
        private $dbhost = '//myhost';
        private $dbuser = '//myuser';
        private $dbpass = '//mypass';
        private $dbname = '//myname';

        // Connect
        public function connect(){
            $mysql_connect_str = "mysql:host=$this->dbhost;dbname=$this->dbname";
            $dbConnection = new PDO($mysql_connect_str, $this->dbuser, $this->dbpass);
            $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return $dbConnection;
        }
    }
    ?>

I just realized the problem was that you omitted to put `$plant` in quotation marks in the query in your original question. You might want to add that to this answer, so it’s clear for other users. — elixenide, Jul 17 '18 at 13:38

SQLSTATE[42S22]: Column not found in REST Api

1 Answers1