Error Inserting data in Database using Prepared Statement

Question

I'm working in a ticket system for my company using PHP and Prepared Statement. When you add a ticket, you're supposed to fill these fields:

1. Ticket type
2. Ticket title
3. Ticket description
4. Date requested
5. Hour requested
6. Company
7. Type of visit
8. Priority
9. Status
10. Technician assigned

This works: 1. You can select the type of ticket pulled from the database. 2. You can select the company pulled from the database. 3. You can select the type of visit pulled from the database. 4. You can select the technicians pulled from the database.

The issue is that when you press on add ticket, it won't add anything to the database.

Here's my code:

newticket.php

<?php
   $projects = ProjectData::getAll();
   $priorities = PriorityData::getAll();
   $ticket= TicketData::getAll();
   $statuses = StatusData::getAll();
   $kinds = KindData::getAll();
   $users = UserData::getAll();

   ?>
<div class="row">
   <div class="col-md-12">
      <div class="card">
         <div class="card-header" data-background-color="blue">
            <h4 class="title">Nuevo Ticket</h4>
         </div>
         <div class="card-content table-responsive">
            <form class="form-horizontal" role="form" method="post" action="./?action=addticket">
               <div class="form-group">
                  <label for="inputEmail1" class="col-lg-2 control-label">Tipo</label>
                  <div class="col-lg-10">
                     <select name="kind_id" class="form-control" required>
                        <?php foreach($kinds as $p):?>
                        <option value="<?php echo $p->id; ?>"><?php echo $p->name; ?></option>
                        <?php endforeach; ?>
                     </select>
                  </div>
               </div>
               <div class="form-group">
                  <label for="inputEmail1" class="col-lg-2 control-label">Titulo</label>
                  <div class="col-lg-10">
                     <input type="text" name="title" required class="form-control" id="inputEmail1" placeholder="Titulo">
                  </div>
               </div>
               <div class="form-group">
                  <label for="inputEmail1" class="col-lg-2 control-label">Descripcion</label>
                  <div class="col-lg-10">
                     <textarea class="form-control" name="description" required placeholder="Descripcion"></textarea>
                  </div>
               </div>
               <div class="form-group">
                  <label for="inputEmail1" class="col-lg-2 control-label">Fecha de la Visita</label>
                  <div class="col-lg-4">
                     <input name="date_at" id="date_at" class="form-control" type="date">
                  </div>
                  <label for="inputEmail1" class="col-lg-2 control-label">Hora de la Visita</label>
                  <div class="col-lg-4">
                     <input name="time_at" id="time_at" class="form-control" type="time" />
                  </div>
               </div>
               <div class="form-group">
                  <label for="inputEmail1" class="col-lg-2 control-label">Proyecto</label>
                  <div class="col-lg-4">
                     <select name="project_id" class="form-control" required>
                        <option value="">-- SELECCIONE --</option>
                        <?php foreach($projects as $p):?>
                        <option value="<?php echo $p->id; ?>"><?php echo $p->name; ?></option>
                        <?php endforeach; ?>
                     </select>
                  </div>
                  <label for="inputEmail1" class="col-lg-2 control-label">Categoria</label>
                  <div class="col-lg-4">
                     <select name="category_id" class="form-control" required>
                        <option value="">-- SELECCIONE --</option>
                        <?php foreach(CategoryData::getAll() as $p):?>
                        <option value="<?php echo $p->id; ?>"><?php echo $p->name; ?></option>
                        <?php endforeach; ?>
                     </select>
                  </div>
               </div>
               <div class="form-group">
                  <label for="inputEmail1" class="col-lg-2 control-label">Prioridad</label>
                  <div class="col-lg-4">
                     <select name="priority_id" class="form-control" required>
                        <option value="">-- SELECCIONE --</option>
                        <?php foreach($priorities as $p):?>
                        <option value="<?php echo $p->id; ?>"><?php echo $p->name; ?></option>
                        <?php endforeach; ?>
                     </select>
                  </div>
                  <label for="inputEmail1" class="col-lg-2 control-label">Estado</label>
                  <div class="col-lg-4">
                     <select name="status_id" class="form-control" required>
                        <?php foreach($statuses as $p):?>
                        <option value="<?php echo $p->id; ?>"><?php echo $p->name; ?></option>
                        <?php endforeach; ?>
                     </select>
                  </div>
               </div>
               <div class="form-group">
                  <label for="inputEmail1" class="col-lg-2 control-label">Asignar a</label>
                  <div class="col-lg-4">
                     <select name="tecnico_id" class="form-control" required>
                        <option value="">-- SELECCIONE --</option>
                        <?php foreach($users as $p):?>
                        <option value="<?php echo $p->id; ?>"><?php echo $p->name." ".$p->lastname; ?></option>
                        <?php endforeach; ?>
                     </select>
                  </div>
               </div>
               <div class="form-group">
                  <div class="col-lg-offset-2 col-lg-10">
                     <button type="submit" class="btn btn-default">Agregar Ticket</button>
                  </div>
               </div>
            </form>
         </div>
      </div>
   </div>
</div>

ticketdata.php

<?php
class TicketData {
    public static $tablename = "ticket";


    public function TicketData(){
        $this->name = "";
        $this->lastname = "";
        $this->email = "";
        $this->password = "";
        $this->date_at="";
        $this->time_at="";
        $this->tecnico_id="";
        $this->created_at = "NOW()";
    }
    public function getTicket(){ return TicketData::getById($this->ticket_id); }
    public function getProject(){ return ProjectData::getById($this->project_id); }
    public function getPriority(){ return PriorityData::getById($this->priority_id); }
    public function getStatus(){ return StatusData::getById($this->status_id); }
    public function getKind(){ return KindData::getById($this->kind_id); }
    public function getCategory(){ return CategoryData::getById($this->category_id); }

    public function add(){
        $sql = "insert into ticket (title,description,date_at,time_at,category_id,project_id,priority_id,user_id,status_id,kind_id,created_at,tecnico_id) ";
        $sql .= "value (\"$this->title\",\"$this->description\",\"$this->date_at\",\"$this->time_at\",\"$this->category_id\",\"$this->project_id\",$this->priority_id,$this->user_id,$this->status_id,$this->kind_id,$this->created_at,$this->tecnico_id)";
        return Executor::doit($sql);
    }

    public static function delById($id){
        $sql = "delete from ".self::$tablename." where id=$id";
        Executor::doit($sql);
    }
    public function del(){
        $sql = "delete from ".self::$tablename." where id=$this->id";
        Executor::doit($sql);
    }

// partiendo de que ya tenemos creado un objecto TicketData previamente utilizamos el contexto
    public function update(){
        $sql = "update ".self::$tablename." set title=\"$this->title\",category_id=\"$this->category_id\",date_at=\"$this->date_at\",time_at=\"$this->time_at\",tecnico_id=\"$this->tecnico_id\",project_id=\"$this->project_id\",priority_id=\"$this->priority_id\",description=\"$this->description\",status_id=\"$this->status_id\",kind_id=\"$this->kind_id\",updated_at=NOW() where id=$this->id";
        Executor::doit($sql);
    }

    public static function getById($id){
        $sql = "select * from ".self::$tablename." where id=$id";
        $query = Executor::doit($sql);
        return Model::one($query[0],new TicketData());
    }

    public static function getRepeated($pacient_id,$medic_id,$date_at,$time_at){
        $sql = "select * from ".self::$tablename." where pacient_id=$pacient_id and medic_id=$medic_id and date_at=\"$date_at\" and time_at=\"$time_at\"";
        $query = Executor::doit($sql);
        return Model::one($query[0],new TicketData());
    }



    public static function getByMail($mail){
        $sql = "select * from ".self::$tablename." where mail=\"$mail\"";
        $query = Executor::doit($sql);
        return Model::one($query[0],new TicketData());
    }

    public static function getEvery(){
        $sql = "select * from ".self::$tablename;
        $query = Executor::doit($sql);
        return Model::many($query[0],new TicketData()); 
    }

    public static function getEvents(){
        $sql = "select * from ".self::$tablename;
        $query = Executor::doit($sql);
        return Model::many($query[0],new TicketData());
    }

    public static function getAll(){
        $sql = "select * from ".self::$tablename." order by created_at desc";
        $query = Executor::doit($sql);
        return Model::many($query[0],new TicketData());
    }

    public static function getAllPendings(){
        $sql = "select * from ".self::$tablename." where status_id=1";
        $query = Executor::doit($sql);
        return Model::many($query[0],new TicketData());
    }


    public static function getAllByPacientId($id){
        $sql = "select * from ".self::$tablename." where pacient_id=$id order by created_at";
        $query = Executor::doit($sql);
        return Model::many($query[0],new TicketData());
    }

    public static function getAllByMedicId($id){
        $sql = "select * from ".self::$tablename." where medic_id=$id order by created_at";
        $query = Executor::doit($sql);
        return Model::many($query[0],new TicketData());
    }

    public static function getBySQL($sql){
        $query = Executor::doit($sql);
        return Model::many($query[0],new TicketData());
    }

    public static function getOld(){
        $sql = "select * from ".self::$tablename." where date(date_at)<date(NOW()) order by date_at";
        $query = Executor::doit($sql);
        return Model::many($query[0],new TicketData());
    }

    public static function getLike($q){
        $sql = "select * from ".self::$tablename." where title like '%$q%'";
        $query = Executor::doit($sql);
        return Model::many($query[0],new TicketData());
    }


}

?>

UPDATE

Made slight changes to TicketData.php correcting @smith's and @Nick's observations. They look like this:

class TicketData {
    public static $tablename = "ticket";
    public function TicketData(){
        $this->name = "";
        $this->title = "";
        $this->description= "";
        $this->lastname = "";
        $this->email = "";
        $this->password = "";
        $this->date_at="";
        $this->time_at="";
        $this->tecnico_id="";
        $this->created_at = "NOW()";
    }
    public function getProject(){ return ProjectData::getById($this->project_id); }
    public function getPriority(){ return PriorityData::getById($this->priority_id); }
    public function getStatus(){ return StatusData::getById($this->status_id); }
    public function getKind(){ return KindData::getById($this->kind_id); }
    public function getCategory(){ return CategoryData::getById($this->category_id); }
    public function add(){
        $sql = "insert into ticket (title,description,date_at,time_at,category_id,project_id,priority_id,user_id,status_id,kind_id,created_at,tecnico_id) ";
        $sql .= "values (\"$this->title\",\"$this->description\",\"$this->date_at\",\"$this->time_at\",\"$this->category_id\",\"$this->project_id\",\"$this->priority_id\",\"$this->user_id\",\"$this->status_id\",\"$this->kind_id\",\"$this->created_at\",\"$this->tecnico_id\")";
        return Executor::doit($sql);
    }

Now, it will save these fields:

1. Ticket type (kind_id)
2. Ticket title (title)
3. Ticket description (description)
4. Date requested (date_at)
5. Hour requested (hour_at)
6. Company (project_id)
7. Type of visit (category_id)
8. Priority (priority_id)
9. Status (status_id)

It won't save this field:

1. Technician assigned (tecnico_id)

addticket-action.php

    <?php
        $r = new TicketData();
        $r->title = $_POST["title"];
        $r->description = $_POST["description"];
        $r->category_id = $_POST["category_id"];
        $r->project_id = $_POST["project_id"];
        $r->priority_id = $_POST["priority_id"];
        $r->user_id = $_SESSION["user_id"];
        $r->status_id = $_POST["status_id"];
        $r->kind_id = $_POST["kind_id"];
        $r->date_at = $_POST["date_at"];
        $r->time_at = $_POST["time_at"];
        $r->tecnico_id = $_POST["tecnico_id"];
        $r->created_at = $_POST["created_at"];
        $r->add();
        Core::alert("Successfully added!");
        Core::redir("./index.php?view=tickets");
 ?>

I want to make everything work before sanitizing and converting to a proper prepared statement. What do I need to correct/add to make the script save the (date_at) (hour_at) and (tecnico_id) fields?

Answer 1

Providing some logs or backend error messages could be pretty helpful in troubleshooting this issue.

At first glance though, the major thing that jumps out is that you're not actually using a prepared statement. You're basically concatenating a string together to make a SQL statement, which is very bad for a couple reasons:

1. You're vulnerable to SQL injection. For example, if you put ","",""); DROP TABLE ticket; -- into your title field, someone could nuke your ticket table because your code doesn't check for this.
2. You need to sanitize your inputs. If title contains a double quote, it will prematurely end your string input, causing your SQL to fail.

This is a pretty big security hole, so plug that up and save yourself some input sanitization headaches at the same time! If you convert to prepared statement and it works, then it was probably a sanitization issue. If it still doesn't work, get some logging statements in there and let's see what you have.

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php https://www.w3schools.com/php/php_mysql_prepared_statements.asp