[php] Inserting row into MySQL Table - Only inserts one field

[gtg93]

Hi All,

 

I am playing around with PHP, SQL and CSS, and have built a very basic inventory system whilst doing so.

 

It's all working fairly well, apart from when trying to insert a row into a table of the SQL DB. The row inserts, but it only fills in one of the fields - has anyone any ideas why this might be happening?

 

 

 

Files are attached below:

 

Apologies for the shocking formatting - just how I've separated things up for now, whilst I get to grips with it.

 

update.html - this is the form to add new entries to the table

</pre><form action="insert.php" method="post">
Network Name: 

Make: 

Model: 

CPU: 

RAM: 

HDD: 

Serial: 

Location: 

Main User: 

Purchase Date: 

Warranty End Date: 

Cost: 

Notes: 


</for

 

 

view.php - This shows the table

include 'config/header.php';
?>






Network Name
Manufacturer
Model
CPU
RAM
Serial No.
Location
Main User
Purchase Date
Warranty End Date
Cost When New
Other Notes








include 'config/db.php';

mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM stafflaptops";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();



echo "Inventory

";

$i=0;
while ($i < $num) {

$networkname=mysql_result($result,$i,"Network Name");
$make=mysql_result($result,$i,"Manufacturer");
$model=mysql_result($result,$i,"Model");
$cpu=mysql_result($result,$i,"CPU");
$ram=mysql_result($result,$i,"RAM");
$hdd=mysql_result($result,$i,"HDD");
$serial=mysql_result($result,$i,"Serial");
$location=mysql_result($result,$i,"Location");
$mainuser=mysql_result($result,$i,"Main User");
$purchdate=mysql_result($result,$i,"Purchase Date");
$wardate=mysql_result($result,$i,"Warranty End");
$cost=mysql_result($result,$i,"Cost When New");
$notes=mysql_result($result,$i,"Notes");


?>




 
 echo $networkname; ?>
 echo $make." ".$model; ?>
 echo $cpu; ?>
 echo $ram; ?>
 echo $hdd; ?>
 echo $serial; ?>
 echo $location; ?>
 echo $mainuser; ?>
 echo $purchdate; ?>
 echo $wardate; ?>
 echo $cost; ?>
 echo $notes; ?>


 






$i++;
}

?>










include 'config/footer.php';
?>

 

 

 

insert.php - This is the PHP script referenced by the html form whilst inserting

include 'config/db.php';

$networkname=$_POST['Network Name'];
$make=$_POST['Manufacturer'];
$model=$_POST['Model'];
$cpu=$_POST['CPU'];
$ram=$_POST['RAM'];
$hdd=$_POST['HDD'];
$serial=$_POST['Serial'];
$location=$_POST['Location'];
$mainuser=$_POST['Main User'];
$purchdate=$_POST['Purchase Date'];
$wardate=$_POST['Warranty End'];
$cost=$_POST['Cost When New'];
$notes=$_POST['Notes'];

mysql_connect($host,$username,$password);


@mysql_select_db($database) or die( "Unable to select database");




$query = "INSERT INTO stafflaptops VALUES('$networkname','$make','$model','$cpu','$ram','$hdd','$serial','$location','$mainuser','$purchdate','$wardate','$cost','$notes')";
mysql_query($query);




mysql_close();
?>

 

 

 

 

The system is obviously seeing and authenticating with the DB properly, as the row inserts and some of the values... but that's it... No errors or anything appearing in any logs - Any help appreciated!

Edited July 6, 2015 by gtg93

[jinnantonnixx]

You've only supplied one row of values in your insert statement.

 

I think you should be using the insert...select construct rather than the straightforward Insert.

 

MySQL :: MySQL 5.5 Reference Manual :: 13.2.5.1 INSERT ... SELECT Syntax

[cullingsh]

Your insert.php file has the $_POST to get the values from the update.html, but the names in the form don't match the$_POST values, the only one that does is Manufacturer.

 

eg:

$networkname=$_POST['networkname'];

$make=$_POST['Manufacturer'];

$model=$_POST['model'];

$cpu=$_POST['cpu'];

$ram=$_POST['ram'];

$hdd=$_POST['hdd'];

$serial=$_POST['serial'];

$location=$_POST['location'];

$mainuser=$_POST['mainuser'];

$purchdate=$_POST['purchasedate'];

$wardate=$_POST['wardate'];

$cost=$_POST['cost'];

$notes=$_POST['notes'];

Edited July 6, 2015 by cullingsh

[cullingsh]

Further, I would use:

 

$query = "INSERT INTO stafflaptops (networkname, make, model, cpu, ram, hdd, serial, location, mainuser, purchdate, wardate, cost, notes) VALUES ('$networkname', '$make', '$model', '$cpu', '$ram', '$hdd', '$serial', '$location', '$mainuser', '$purchdate', '$wardate', '$cost', '$notes')";

 

 

that way, it will tie up the values with the field names in your database, so if you ove fields round or add more, you wont have to change your script, other than any new/removed fields.

[pcstru]

On a slight tangent, all the code listed and suggested is vulnerable to SQL injection attacks. You should consider using prepared statements and/or sanitising user inputs.

[gtg93]

Your insert.php file has the $_POST to get the values from the update.html, but the names in the form don't match the$_POST values, the only one that does is Manufacturer.

 

eg:

$networkname=$_POST['networkname'];

$make=$_POST['Manufacturer'];

$model=$_POST['model'];

$cpu=$_POST['cpu'];

$ram=$_POST['ram'];

$hdd=$_POST['hdd'];

$serial=$_POST['serial'];

$location=$_POST['location'];

$mainuser=$_POST['mainuser'];

$purchdate=$_POST['purchasedate'];

$wardate=$_POST['wardate'];

$cost=$_POST['cost'];

$notes=$_POST['notes'];

 

You have no idea how any times I checked this !! Cheers

[ZeroHour]

On a slight tangent, all the code listed and suggested is vulnerable to SQL injection attacks. You should consider using prepared statements and/or sanitising user inputs.

 

As pcstru said, you want to run it through PHP: filter_input - Manual or PDO before doing anything with the data.

E.G.

$var = filter_input(INPUT_POST, 'varname',FILTER_SANITIZE_STRING);

Edited July 6, 2015 by ZeroHour

[gtg93]

On a slight tangent, all the code listed and suggested is vulnerable to SQL injection attacks. You should consider using prepared statements and/or sanitising user inputs.

 

 

As pcstru said, you want to run it through PHP: filter_input - Manual or PDO before doing anything with the data.

E.G.

$var = filter_input(INPUT_POST, 'varname',FILTER_SANITIZE_STRING);

 

 

Thanks for the links - I'm aware at the moment they're vulnerable, but it's more of me just experimenting and playing about at the moment. Should it become a functioning tool however, or once I've mastered the basics, this is something I'll be looking at

[pcstru]

Thanks for the links - I'm aware at the moment they're vulnerable, but it's more of me just experimenting and playing about at the moment. Should it become a functioning tool however, or once I've mastered the basics, this is something I'll be looking at

IMO sanitising user input in a web application *is* the basics.

[ZeroHour]

IMO sanitising user input in a web application *is* the basics.

 

Knowing about the need and how to do it is basic but when doing RAD you dont need to worry.

[gtg93]

From what I'm reading, am I correct in saying that the sanitisation of user input, basically removes all special characters, so if users were to try and input code, it wouldn't run?

 

 

If so, you think that would sufficiently do the job for a simple application such as an inventory?

[pcstru]

From what I'm reading, am I correct in saying that the sanitisation of user input, basically removes all special characters, so if users were to try and input code, it wouldn't run?

Yes. Typically, an injection attack would look to close off the legitimate statement and then append another statement which might either expose information or manipulate the database. Hence :

 

http://imgs.xkcd.com/comics/exploits_of_a_mom.png

 

If so, you think that would sufficiently do the job for a simple application such as an inventory?

Sanitising input is always good to do when using web forms which drive back end CGI scripts. Using prepared statements should also be more efficient on the database because the SQL is compiled once but might be run many times. With a prepared statement, the presence of an injection attack would throw an error.

[SovietRussia]

Or

 

mysql_real_escape_string to get rid of nasty injections.

[ZeroHour]

Or

 

mysql_real_escape_string to get rid of nasty injections.

 

That is being deprecated right now for mysqli versions.

 

http://php.net/manual/en/function.mysql-real-escape-string.php