Log a call

[MadFly]

HiI am busy trying to setup a "call-logging" website, where I want to be able to add clients into a sql database (which is allready done), and then log a call or problem in another sql table, which is linked to a client from the clients table. So far I have managed to get the Client side (adding, updating and viewing clients) of the website done, and now I am struggling with the call logging part.Below is the code that i have got so far...Helpdesk.html page's form data where i actually log the call on a client name

	<form action="logacall.php" method="post"><table frame="border"><tr><td><!--Date Today:--><img src="img/cal.gif"></td><td><? print(Date("l d F, Y")); ?></td></tr><tr><td><!--Choose Client:--><img src="img/kiesklient-icon.png"></td><?php $con = mysql_connect("localhost","user","pass");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("my_db", $con);$res02 = mysql_query("SELECT * FROM Kliente");?><td><select name="Client"><option value="selected">** Choose Client **</option><?php while( $row = mysql_fetch_row( $res02 )) {						$sel = ( Kliente === $row['FirstName'] ) ? "id='sel' selected" : "";   	 printf ( " <option %s value='%s'>%s</option>\n", $sel, $row[0] , $row[1].' '. $row[2]);  //die data wat select en gedisplay word.					  }; 					  mysql_close($con);					  ?>				  </select>	  </td></tr><tr><td><!--Problem:--><img src="img/info-icon.png"></td><td><!-- cols="40" rows="10" --><textarea name="problem" >give discription of problem here</textarea></td></tr><tr><td><!--Date Done:--><img src="img/cal.gif">Done</td><td><input name="Date_close" id="date_done" type="text" value="">																							  				  <img src="img/cal.gif" title="Click Here" alt="Click Here"  onclick="scwShow(document.getElementById('date_done'),this);"></td>	<td>dd-mm-yyyy</td></tr><tr><td><!--Password:--><img src="img/noaccess-icon.png"></td><td><input type="password" name="txtPass" /></td></tr><td></td><td><input type="submit" value="Log the Call"/>  <input type="reset" value="Oops"></td></table></form>

Code for logacall.php

<?php$txtPass = $_POST['txtPass'];if ($txtPass == "strong_password") {  $con = mysql_connect("localhost","user","pass");  if (!$con)  {	die('Could not connect: ' . mysql_error());  }  $Client = $_POST['Client']  $Date_open = $_POST['Date_open']  $problem = $_POST['problem']  $datum_close = $_POST['datum_close']    mysql_select_db("my_db", $con);  $sql="INSERT INTO Calls(probleemID,klientID,datum_open,probleem,datum_close) VALUES (",'$Client','$datum_open','$probleem','$datum_close'");  //echo $sql;    $result = mysql_query($sql) or die  ("Could not execute query: $sql." . mysql_error());    {	die('Error: ' . mysql_error());  }  else {	echo "Thanks! <br /><br />The call was logged. <br /><br />Go back to <a href=\"index.html\">Index</a>.";  }  mysql_close($con);}else {  echo "You did not enter any password, or it was the wrong one! <br /><br /><br />Try <a href=\"helpdesk.html\">again</a>!";}?>

Now, when i go to the helpdesk.html page, and select a user from the dropdown list (which it gets automatically from the SQL Clients table) add an example problem, like unable to print, and select the date_done(when call should be completed, and click on log the call, it just gives me a blank page with no errors whatsoever. I would also like to add the date of today (when it was logged) there by the Date_Today code.Any ideas on how I would accomplish this, and where the error"s" is the code above?EDIT: The fields I have in the Calls table are as follows: problemID INT unsigned not null primary key auto_incrementclientID INT unsigned references personID (from Clients table)problem varchar (255)date_open datetimedate_close datetime

[justsomeguy]

Several lines are missing semicolons. Since the errors aren't being displayed, you probably have error logging turned on, so you should figure out which log file those errors are going to so that you can look them up there. You can also create a file to set the error settings to display errors and then have it include the file with the errors:

<?phpini_set('display_errors', 1);ini_set('html_errors', 1);error_reporting(E_ALL);include 'logacall.php';?>

[MadFly]

I managed to get it working.Just had to fill in all the missing ; and also after that i got a lot of parse errors, something about T_STRING.but eventually got that sorted out as well.I am now able to log a problem against a client name

[MadFly]

Could anyone perhaps tell me exactly where my error is in the following code?

$sql='UPDATE Calls' . ' SET opmerkings='$_POST['opmerkings']',status='$_POST['status']', . ' WHERE probleemID='$_POST[klient]'"';

I know I am missing some " or ' or ;I have played around with a few options but nothing seems sovle my Parse error: parse error, unexpected T_VARIABLE problem on this piece of codeI have set the DB up so that probleemID references klientIDAnd on this form I plan on using here, I have some code that shows all clients in a dropdown list directly out of the sql db (MYSQL)So now when I log a call on a client, it shows up as clientID instead of firstname and lastname, but it uses the clientID. The showing of clientID instead of firstname and last i can appearantly fix with a join, but thats a problem for later. I first would like to fix this update problem.Edit: ok, I have edited the above code a little looks like this now

$sql=$_POST['opmerkings']."= '".$_POST['status'];$sql="UPDATE Calls SET ".$sql."' WHERE klientID = '".$_POST['probleemID']."'";

But now im having problems with the WHERE statement. How do i get it to update the call where the "problemID' = ClientID. want to update that specific call with whatever is writtten in $_POST['opmerkings'](again) Edit: I need to update both the "opmerkings" (discription) as well as the status (done or still open) of that call

[justsomeguy]

You can write the original query like this:

$sql='UPDATE Calls SET opmerkings=\'' . $_POST['opmerkings'] . '\', status=\'' . $_POST['status'] . '\' WHERE probleemID=\'' . $_POST['klient'] . "'";

That is syntactically correct and it will work, but it's unsecure, it's open to SQL injection attacks. When you're using values from $_POST, $_GET, or $_COOKIE in a SQL query you need to validate and sanitize the values to make sure they aren't trying to attack the database. The mysql_real_escape_string function will protect against that.http://www.php.net/manual/en/function.mysq...cape-string.php

[MadFly]

$sql='UPDATE Calls SET opmerkings=\'' . $_POST['opmerkings'] . '\', status=\'' . $_POST['status'] . '\' WHERE probleemID=\'' . $_POST['klient'] . "'";

That is syntactically correct and it will work, but it's unsecure, it's open to SQL injection attacks. When you're using values from $_POST, $_GET, or $_COOKIE in a SQL query you need to validate and sanitize the values to make sure they aren't trying to attack the database. The mysql_real_escape_string function will protect against that.

Thanks, I tried that, but it still did not work, the php part of it went through sucessfully, but the call never got updated. So I thought if i change the selection, where i select a client, to rather select the problem and status. it worked. So now my dropdown list shows the problem, and the status of the call. About the sql injection and securing part. I have placed a password on the form that i use to create new clients, update clients, log new call, update call, and everything that inserts or updates anything on the sql, if you enter the wrong password, it does not insert anything into the sql db. Can that be considered secure enough?I will now start playing around with an option to update the date when the call is/was closed.One quick other question, how to make it so that once i log a new call, the status will automatically be set to Still Open?

[justsomeguy]

Can that be considered secure enough?

No, the general rule with web development is to never trust user input. Even if the system is password-protected, don't assume that your users will never make a mistake or that one of them wouldn't try to actively attack the system (or that a hacker couldn't gain access to someone's account). The password form itself might be vulnerable, for example. One of the easiest types of a SQL injection attack is to get through a password form without needing to actually enter correct data. If your form has fields for username and password, and your query looks like this:

$sql = "SELECT * FROM users WHERE username='{$_POST['username']}' AND password='{$_POST['password']}'";

Someone could type any username which they know exists, like "admin", and for the password they could type ' OR '1. That would make the query look like this when it goes to the database:SELECT * FROM users WHERE username='admin' AND password='' OR '1'The OR '1' means that it will select any row, so that will select the row for the admin user even though they didn't fill in the correct password. If the script didn't do any more validation, it would just log the person in as admin and send them on their way. If you used mysql_real_escape_string on the password, it would send this query to the database:SELECT * FROM users WHERE username='admin' AND password='\' OR \'1'Just that one change means that now the database is looking for the text ' OR '1 in the password field, and the query will fail as expected.

One quick other question, how to make it so that once i log a new call, the status will automatically be set to Still Open?

If you're inserting a new record into the table, you can set a default value on the field so that if the field wasn't given a specific value it will default to that one. If you're updating a record in the table you just need to update that one field with the value you want.

[MadFly]

I see what you mean.Excuse me if i just lied, and did not actually understand. But would that have the same effect if i only have a password on the form? Like this...

	<form action="insert.php" method="post"><table border="0"><tr><td>Naam:</td><td><input type="text" name="firstname" /></td></tr><tr><td>Van:</td><td><input type="text" name="lastname" /></td></tr><tr><td>Ouderdom:</td><td><input type="text" name="age" /></td></tr><tr><td>Adres:</td><td><input type="text" name="adres" /></td></tr><tr><td>Stad:</td><td><input type="text" name="city" /></td></tr><tr><td>Epos:</td><td><input type="text" name="email" /></td></tr><tr><td>Telefoon:</td><td><input type="text" name="telefoon" /></td></tr><tr><td>Wagwoord:</td><td><input type="password" name="txtPass" /></td></tr><tr><td></td><td><input type="submit" value="Add new user"/>  <input type="reset" value="Oops"></td><td></td></tr></table></form>

Where the actual password to actually insert any data into the database is in the insert.php file. Which if you open that directly from the address bar in you browser, will display a message saying that you have entered the wrong or no password at all.And the general idea behind this website of mine is only for my use. so that i can keep track of what "calls" i do after hours, and for who i have done what. Kind of like a personal helpdesk system. But if you say that this is also not secure enough from a sql injection attack, i will take a decent look at that link you gave in previous post, and/or other methods to "secure" it.There is no user accounts on this website. its just there on the web. if you know the password you can insert data into the sql database. i cannot see any other way that any1 could "hack" it or gain access to the password. but again... if you say that is not secure enough (one can actually NEVER be secure enough)... i'll see if i can find something more secure, starting with that link you gave.

[justsomeguy]

Basically, it's vulnerable if you just use the value from $_POST directly in your query. All you need to do in order to fix that is to use mysql_real_escape_string, e.g.:

$sql = "SELECT * FROM users WHERE username='" . mysql_real_escape_string($_POST['username']) . "' AND password='" . mysql_real_escape_string($_POST['password']) . "'";

If the value isn't going in a query, if you're just checking the password by comparing it right in the file, e.g.:

if ($_POST['password'] == 'the password')

Then there's no reason you need to protect against anything there, this attack is only for data going into a database.Other than protecting against attacks, using mysql_real_escape_string is good to use in general. If you're inserting text in your database which includes single quotes or apostrophes, they will break the query unless you escape them first. If you have a statement like this:

$sql = "INSERT INTO table_name (field1) VALUES ('{$_POST['field1']}')";

If that field1 value contains text like "don't do that", then PHP will send this query to the database:

INSERT INTO table_name (field1) VALUES ('don't do that')

In that case, the apostrophe in "don't" is going to break that query and it will fail. Using mysql_real_escape_string will also fix those problems.

[MadFly]

Cool. I will then add that into all my php file which uses the $_POST function.Thanks again