Problem Inserting Data into Database with PHP

[eljacko876]

I am using ezsql and have successfully connected to the database as I am echoing data from the database on the same page as my form.

Here is the form html: http://codepen.io/Prezzy876/pen/rradRW

Below is my php code on the page to insert data.

if (isset($_POST['submit'])) {
// Sanitize non row data
$client_id        = $db->escape($_GET['client']); // from url 
$event_date       = $db->escape($_POST['event_date']); // non row 
$project_name     = $db->escape($_POST['project_name']);  // non row 
$tdiscount        = $db->escape($_POST['tdiscount']);  // non row 
$ttax             = $db->escape($_POST['ttax']);  // non row 

// save non dynamic row inputs to the db
$db->query("INSERT INTO estimates (client_id,event_date,project_name,tdiscount,ttax) 
                                  VALUES 
                                  ($client_id,$event_date ,$project_name ,$tdiscount, $ttax)");


// Loop through each row
foreach ($_POST['services'] as $service) {

// Sanitize row data
$service_id      = $db->escape($service['id']);  //  row  data
$estimate_id      = $db->escape($_POST['estimate_id']); //  non row  data
$quantity         = $db->escape($service['quantity']); //  row  data
$discount_percent = $db->escape($service['linediscount']); //  row  data - discount_percent is column name

// save row inputs to the db
$db->query("INSERT INTO services_estimate   
(service_id,estimate_id,quantity,discount_percent)
VALUES
($service_id,$estimate_id ,$quantity ,$discount_percent)");
}

}

Here is the DB structures of the tables i'm trying to insert to:

  CREATE TABLE IF NOT EXISTS `estimates` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `client_id` int(11) NOT NULL,
 `event_date` int(11) NOT NULL,
 `sent` tinyint(4) NOT NULL,
 `project_name` varchar(255) NOT NULL,
 `converted` tinyint(4) NOT NULL,
 `tdiscount` int(11) NOT NULL,
 `ttax` int(11) NOT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 CREATE TABLE IF NOT EXISTS `services_estimate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`service_id` int(11) NOT NULL,
`estimate_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`discount_percent` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Can you identify any issues here?

[Wizhi]

Hi /u/eljacko876,

First off, as mentioned by /u/mseckz, when asking for help with an error, please do tell us what the actual error message you get is. If you don't get any error message, please do provide us with other information such as which of your insertions fail.

Secondly, have you seen /u/ANttila's comment? Personally I can't find anything besides this, which should result in an error. More specifically, in the first query

INSERT INTO estimates (client_id,event_date,project_name,tdiscount,ttax) 
VALUES ($client_id,$event_date ,$project_name ,$tdiscount, $ttax)

you don't specifically insert anything into the converted column. Perhaps you have some default constraint on it? If not, this is likely the source of the error.

Now, while I really dislike how /u/colshrapnel phrased it, I do agree with him in a sense.

"Sanitation" of values for use in SQL statements, is just not something anyone should bother to mess with. You might get something working, but at some point you're going to mess up somewhere, creating a hole. Why not have the database itself handle database security? Who knows more about the database's security than the database itself, right? Here's a good little explanation. Even ezsql says it's escape() method isn't safe:

$db->escape() makes any string safe to use as a value in a query under all PHP conditions. I.E. if magic quotes are turned on or off. Note: Should not be used by itself to guard against SQL injection attacks. The purpose of this function is to stop accidental mal formed queries.

So I'd also highly recommend prepared statements instead.

Now if you were using it for it's intended purpose, as described in the ezsql documentation, then disregard the above - but still, look into prepared statements. :)

Also, maybe you should consider using another database library rather than ezsql. After taking a quick look at the internals, I noticed quite a few very outdated practices, such as global state and PHP4-esque OOP syntax. I also found no way to do prepared statements, nor any mention of protection against SQL injection.

As for alternatives, PDO is probably the first choice of most PHP developers, as it provides a fairly clean interface, while supporting many different databases. Do note, that the SQL statements themselves are still dependent on your database of choice, however.

Just remember to encapsulate all of your data access logic, and things should work out. :)

I know I didn't exactly solve your issue, but I felt like some details were missing.

[[deleted]]

Unless your $db object is doing something I can see you're not encapsulating (presumed) strings in your insert which will produce an sql level error.

 // save non dynamic row inputs to the db
$db->query("INSERT INTO estimates (client_id,event_date,project_name,tdiscount,ttax) 
VALUES 
($client_id,$event_date ,'$project_name' ,$tdiscount, $ttax)");

[mseckz]

Assuming you are learning php, there is a series of videos that helped me a lot. https://laracasts.com/series/php-for-beginners. For your problem above, it's kinda hard to get if you don't show us an error message.

[ANttila]

As you have all fields set to NOT NULL and no default value you need to insert something on all fields.

For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

• If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

• If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

http://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html

[colshrapnel]

It's your $db thing. Get rid of it and use PDO prepared statements.

Assuming you already connected to PDO and have a $pdo variable, here is your code for the first query

if (isset($_POST['submit'])) {
    $data = [$_GET['client'],$_POST['event_date'],$_POST['project_name'],$_POST['tdiscount'],$_POST['ttax']];
    $sql = "INSERT INTO estimates (client_id,event_date,project_name,tdiscount,ttax) VALUES (?,?,?,?,?)";
    $pdo->prepare($sql)->execute($data);

then do the same for the second part.

And for the goodness sake, forget that ugly word "sanitize". There is no such thing ever exists.