Page 1 of 2

Data not making it into mysql database

Posted: Mon Nov 23, 2009 4:03 pm
by tinyang
Hello, I read on how to post here and so I am providing the below info:

Magic quotes are disabled
PHP Version: 5.1.6
Display Errors: Off
Error Level: Not E_ALL
Register Globals: Off

Here is my issue:

I have a form on a webpage with a hidden input field, and a select field. I am using jquery to collect the data from those fields and post it to my php script which inserts it into the database. I'm pretty sure my javascript is working because I can use the print_r() command and it has the correct, updated data values from my fields everytime I test it, but the data is not getting into the table so I suspect my php script has an issue, but I can't find it. Below is the php script, I would be grateful for any help!

Code: Select all

<?php
session_start();
 
$rating = $_POST['rating'];
$pic_id = $_POST['pic_id'];
 
//Connect and select database
    mysql_connect("192.168.000.000", "username", "password") or die(mysql_error());
    mysql_select_db("tutorial") or die(mysql_error());
 
    $query="INSERT INTO comments (pic_id, user, rating) VALUES ('".$pic_id."', '".$_SESSION['username']."', '".$rating."')";
 
    mysql_query($query) or die(mysql_error());
?>

Re: Data not making it into mysql database

Posted: Mon Nov 23, 2009 4:14 pm
by iankent
Make sure your query is correct - I'd echo out the query and try running it manually via phpmyadmin/whatever to make sure it works and there's no errors. If it isn't working for any reason I'd expect it to die with mysql_error but might as well double check.

Strictly speaking it should have a semi-colon to end the SQL query, and I wouldn't put quotes around the values if they're going into a numerical column (less hassle for mysql to deal with!). Something you should do is validate the values from $_POST and escape them!

edit:
depending on your server, you may not be seeing PHP errors, so if the script doesn't output anything to confirm it was successful you may never know that it wasn't. on most live servers you'll need to check the error logs rather than expect errors to be output on the page

Re: Data not making it into mysql database

Posted: Thu Nov 26, 2009 12:38 pm
by tinyang
iankent wrote:Make sure your query is correct - I'd echo out the query and try running it manually via phpmyadmin/whatever to make sure it works and there's no errors. If it isn't working for any reason I'd expect it to die with mysql_error but might as well double check.
good advice! I tried it in mysql query browser and it works fine.
iankent wrote:Strictly speaking it should have a semi-colon to end the SQL query, and I wouldn't put quotes around the values if they're going into a numerical column (less hassle for mysql to deal with!).
I updated my script with these recommendations, and it still is not working, but good advice anyway.
iankent wrote:Something you should do is validate the values from $_POST and escape them!
You are absolutely correct! I was hoping to get it working before adding security to it, I'm not sure I understand the proper use of magic quotes and MRES yet.
iankent wrote:edit:
depending on your server, you may not be seeing PHP errors, so if the script doesn't output anything to confirm it was successful you may never know that it wasn't. on most live servers you'll need to check the error logs rather than expect errors to be output on the page
Another good point, where would I find the logs? My server is fedora running apache.

Thanks so much for your help! Below is the updated script. Any other suggestions to get this working are also welcome. :)

Code: Select all

<?php
session_start();
 
$rating = $_POST['rating'];
$pic_id = $_POST['pic_id'];
 
//Connect and select database
    mysql_connect("192.168.000.000", "username", "password") or die(mysql_error());
    mysql_select_db("tutorial") or die(mysql_error());
 
    $query="INSERT INTO comments (pic_id, user, rating) VALUES ('.$pic_id.', '".$_SESSION['username']."', '.$rating.';)";
 
    mysql_query($query) or die(mysql_error());
 
?>

Re: Data not making it into mysql database

Posted: Thu Nov 26, 2009 12:56 pm
by iankent
tinyang wrote:You are absolutely correct! I was hoping to get it working before adding security to it, I'm not sure I understand the proper use of magic quotes and MRES yet.
It's worth just putting it in from the start, it can help avoid mysql errors such as this one.
Basically, if the variable you're expecting is a number, make sure its a number or cast it to one. By ensuring it's a number you know its safe to put in your query without quotes, e.g.:

Code: Select all

 
$num = $_POST['varname'];
if(!is_numeric($num)) {
    // its not a number - either set to a default number, or exit with an error
    $num = 0;
}
// alternatively, cast to a float or integer:
$num = (int)$_POST['varname']; // will return 0 if $_POST['varname'] isn't a number
$num = (float)$_POST['varname']; // will also return 0 if $_POST['varname'] isn't a number
 
It's a good idea to apply mysql_real_escape_string to all variables going into a query, regardless of whether its a number, date, binary or anything else! that way any quotes and other special characters will be escaped so MySQL knows its part of a query value.

The following example shows why this is so important. It's intended to get the row from the mysql table but only where the username matches $var.

Code: Select all

 
$var = $_POST['username'];
$qry = "SELECT * FROM mytable WHERE username='$var';";
mysql_query($qry);
 
But, what if somebody submits

Code: Select all

' OR 1=1;#
as the value of $_POST['username']? Well, the query becomes this:

Code: Select all

SELECT * FROM mytable WHERE username = '' OR 1=1;#';
which will return all rows, as in every case 1=1 will be true. Everything after the # is ignored.

If you'd run mysql_real_escape_string on $var before passing it to the query, the quotes will have been escaped and you're query would function as intended.
tinyang wrote:Another good point, where would I find the logs? My server is fedora running apache.
hard to say. my fedora server was putting them into /var/logs/ iirc, though my ubuntu one puts them somewhere different. is it a server you've setup yourself or a dedicated/shared server? if the former, have a look in your apache config files for the path. if the latter, you may be able to find out using phpinfo() or your hosts help files.

And finally, this line doesn't look quite right:

Code: Select all

$query="INSERT INTO comments (pic_id, user, rating) VALUES ('.$pic_id.', '".$_SESSION['username']."', '.$rating.';)";
I don't think there should be a semi-colon before the closing bracket at the end. It should be after the bracket not before, e.g.

Code: Select all

$query="INSERT INTO comments (pic_id, user, rating) VALUES ('.$pic_id.', '".$_SESSION['username']."', '.$rating.');";
Other than that, unless one of the values of either $pic_id, $rating or $_SESSION['username'] are incorrect, I can't see anything obviously wrong with your code.

Instead of using die(mysql_error()) it may be worth outputting it manually, e.g.

Code: Select all

 
$qry = 'select whatever blah blah';
$res = mysql_query($qry);
if(mysql_error()) {
    echo "MySQL error: ".mysql_error();
}
 
(do the same with your mysql_connect and mysql_select_db lines too!). that should force the error to go to output rather than the error logs

hth

Re: Data not making it into mysql database

Posted: Thu Nov 26, 2009 2:39 pm
by tinyang
iankent wrote:It's a good idea to apply mysql_real_escape_string to all variables going into a query, regardless of whether its a number, date, binary or anything else! that way any quotes and other special characters will be escaped so MySQL knows its part of a query value.

The following example shows why this is so important.
I understand why, it's how to implement it that I don't understand, but thank you for the detailed why explanation anyway. :)

Here is another question, considering the inputs on this page are a select menu and input from a hidden field (meaning the input is controlled), would they need to have the security applied to them? I could be wrong, but I would think it would only matter for fields where the user can free-form type in their string in the field. Not trying to negate your advice mind you, just making sure I have a comprehensive security viewpoint here for fields in forms.
iankent wrote:I don't think there should be a semi-colon before the closing bracket at the end... I can't see anything obviously wrong with your code.
I've corrected the semi-colon placement as you advised. I know, I can't see anything wrong either - it's driving me nuts, but I'm trying to be patient with myself and work through it.
iankent wrote:(do the same with your mysql_connect and mysql_select_db lines too!). that should force the error to go to output rather than the error logs
the rating.php script does not output/write anything to a webpage, it just posts the data to the database (but I am going to try this method anyway). I don't mind looking in the logs, I found the log I needed to check thanks to your help. :)

Here is my post array I'm sending to the rating php script (as echoed through a print_r() command):

Code: Select all

Array ( [rating] => 3 [pic_id] => 28 )
and here is the output from the log when I post from my webpage:

Code: Select all

66.73.xxx.xxx - - [26/Nov/2009:12:23:08 -06:00] "POST /cam/image.php?img_id=28 HTTP/1.1" 200 2436
I'm still not seeing anything amiss. If anyone has anymore advice for me or something I could check, it would be very appreciated!

Re: Data not making it into mysql database

Posted: Thu Nov 26, 2009 2:47 pm
by iankent
tinyang wrote:I understand why, it's how to implement it that I don't understand, but thank you for the detailed why explanation anyway. :)
very simple, you just do:

Code: Select all

$var = mysql_real_escape_string($var);
*after* you've connected to the database. That's all there is to it :)
tinyang wrote:Here is another question, considering the inputs on this page are a select menu and input from a hidden field (meaning the input is controlled), would they need to have the security applied to them? I could be wrong, but I would think it would only matter for fields where the user can free-form type in their string in the field. Not trying to negate your advice mind you, just making sure I have a comprehensive security viewpoint here for fields in forms.
Unfortunately not, even though your HTML form is only giving a limited few options doesn't mean the user won't manually submit whatever they like using HTTP POST directly. I.e., the user may never even have viewed your form and could be submitting data directly. It's always wise not to trust *any* data when including it in an SQL query, including variables from $_SERVER, $_COOKIE and $_SESSION
tinyang wrote: the rating.php script does not output/write anything to a webpage, it just posts the data to the database (but I am going to try this method anyway). I don't mind looking in the logs, I found the log I needed to check thanks to your help. :)
It looks as though the info is being posted to the rating.php script ok if print_r is outputting that, so it appears theres a problem with your SQL bit for whatever reason.
if you're able to redirect mysql errors to the output then that'll be the easiest way, but if not, you could write any errors to file using fopen/fwrite/fclose functions - easier than trawling through your error logs!

when you say it posts data to the database, what happens then? if a form is submitting to rating.php then either rating.php is redirecting you to another page or its' outputting something?

Re: Data not making it into mysql database

Posted: Thu Nov 26, 2009 3:46 pm
by tinyang
iankent wrote:very simple, you just do:

Code: Select all

$var = mysql_real_escape_string($var);
*after* you've connected to the database. That's all there is to it :)
Like this:

Code: Select all

<?php
session_start();
 
$rating = $_POST['rating'];
$pic_id = $_POST['pic_id'];
 
//Connect and select database
    mysql_connect("192.168.000.000", "username", "password") or die(mysql_error());
    mysql_select_db("tutorial") or die(mysql_error());
 
$rating = mysql_real_escape_string($rating);
$pic_id = mysql_real_escape_string(pic_id);
 
    $query="INSERT INTO comments (pic_id, user, rating) VALUES ('.$pic_id.', '".$_SESSION['username']."', '.$rating.');";
 
    //mysql_query($query) or die(mysql_error());
 
mysql_query($query);
 
if(mysql_error()) {
    echo "MySQL error: ".mysql_error();
}
?>
iankent wrote:Unfortunately not, even though your HTML form is only giving a limited few options doesn't mean the user won't manually submit whatever they like using HTTP POST directly. I.e., the user may never even have viewed your form and could be submitting data directly. It's always wise not to trust *any* data when including it in an SQL query, including variables from $_SERVER, $_COOKIE and $_SESSION
OK, I understand, and I will MRES all input then. Thanks for explaining it to a newbie like me.
iankent wrote:It looks as though the info is being posted to the rating.php script ok if print_r is outputting that, so it appears theres a problem with your SQL bit for whatever reason.
That seems likely.
iankent wrote:when you say it posts data to the database, what happens then? if a form is submitting to rating.php then either rating.php is redirecting you to another page or its' outputting something?
It stays on the same webpage, it just refreshes the same webpage (and updates the print_r() with the correct post data) when you press the form button to submit your rating. The code I post for my rating.php script is the entire script, I am not leaving anything out. I hope this answers your question and thank you so much for your continued help!

Re: Data not making it into mysql database

Posted: Thu Nov 26, 2009 3:55 pm
by iankent
Nearly got it :) All variables must be run through mysql_real_escape_string, but in addition to that, if its a number you're inserting into the database and you're not enclosing it in quotes in the query, you need to make sure its a number first (otherwise your escaped string could be formatted to insert unwanted values)

I've changed your code to output mysql errors to screen in all cases, and also to add the number validation. Hope it makes sense, ask if you need any more info :)

Code: Select all

<?php
session_start();
 
$rating = $_POST['rating'];
$pic_id = $_POST['pic_id'];
 
//Connect and select database
// We'll do the mysql_error() bit on these two as well, the problem could be here
mysql_connect("192.168.000.000", "username", "password");
if(mysql_error()) {
    echo "mysql_connect error: ".mysql_error();
}
mysql_select_db("tutorial");
if(mysql_error()) {
    echo "mysql_select_db error: ".mysql_error();
}
 
if(!is_numeric($rating) || !is_numeric($pic_id)) {
    // either make them a number, or display an error and exit
    echo "Either rating or pic_id isn't a valid number";
    exit;
}
// Even though we know they're definately numbers, there's no harm in escaping them to be on the safe side!
$rating = mysql_real_escape_string($rating);
$pic_id = mysql_real_escape_string(pic_id);
 
// Personally, I'd escape $_SESSION['username'] too. Even though it shouldn't be able to be changed by the end-user, you can't guarantee it hasn't been, so best to play it safe
$username = mysql_real_escape_string($_SESSION['username']);
 
// Just noticed why your query might not be working... you've removed the wrong quote from around your numbers, instead of taking away the doubles you should have taken away the singles. Actually, in this case, you can take both of them away - because the whole query is inside double quotes, the variables can be parsed inline like this
$query="INSERT INTO comments (pic_id, user, rating) VALUES ($pic_id, '$username', $rating);";
 
    //mysql_query($query) or die(mysql_error());
 
mysql_query($query);
 
if(mysql_error()) {
    echo "mysql_query error: ".mysql_error();
}
?>
try that and see if you get any errors :)

Re: Data not making it into mysql database

Posted: Thu Nov 26, 2009 8:24 pm
by tinyang
Thanks for the updated script! But it gave me no output on the webpage. I'm trying to find the log file specified in the phpinfo.php page on my site (php.log) but it's not there. when I look in /var/log/httpd, there is no php.log file.

Re: Data not making it into mysql database

Posted: Fri Nov 27, 2009 1:38 am
by iankent
Try adding this as the first line of PHP code in the file, see if it makes any difference:

Code: Select all

error_reporting(-1);

Re: Data not making it into mysql database

Posted: Fri Nov 27, 2009 8:29 am
by tinyang
Hi iankent,

I tried your latest suggestion and I'm still not getting any more feedback on the webpage. I also tried clearing the cache and closing and reopening the browser. I'm not quite sure why that would help because we are dealing with a server side language, but I figured trying couldn't hurt. Alas, still no joy. Would it be helpful for you to look at the site? It's password protected, but maybe doing that would help you help me. :) Let me know. Thanks again for your continued help!!

Re: Data not making it into mysql database

Posted: Fri Nov 27, 2009 10:08 am
by iankent
tinyang wrote:Would it be helpful for you to look at the site? It's password protected, but maybe doing that would help you help me. :) Let me know. Thanks again for your continued help!!
I don't honestly think it will but I'm happy to take a look if you want - PM me any details I'd need.

It's a very odd one this. I'd expect at least some kind of error to tell you whats happening.

Could you try changing the username/password/database name to something obviously wrong and see whether that generates any errors? If it doesn't, there's something else causing the lack of info. If it does, we at least know mysql is connecting properly.

Re: Data not making it into mysql database

Posted: Fri Nov 27, 2009 10:45 am
by tinyang
You are quite correct, I changed the credentials for logging into the mysql database in the rating.php script, and I saw no errors! What is the next step? I did pm you the access info for the website, so if you would like to take a look, feel free.

Re: Data not making it into mysql database

Posted: Fri Nov 27, 2009 10:57 am
by iankent
Unfortunately it doesn't really help, as expected its only showing what you'd already told me! Are you using mysql elsewhere on the site successfully?

If not, could you try creating a blank file and run phpinfo(), and have a look through the output to check for mysql references. There should be a whole section dedicated to it :) copy and paste it here (taking care to remove any sensitive info!). You may also find a bit under Apache configuration mentioning the mysql bin path.

My only thought is that possibly, for whatever reason, the mysql extension hasn't been installed in PHP (or isn't being loaded). That'd explain why its not working, but wouldn't explain the lack of errors!

Re: Data not making it into mysql database

Posted: Fri Nov 27, 2009 11:08 am
by tinyang
iankent wrote:Unfortunately it doesn't really help, as expected its only showing what you'd already told me! Are you using mysql elsewhere on the site successfully?
Yes, I have mysql working on the rest of the site correctly. One example is the pagination of the thumbnails in the gallery, but also it is actively adding data to my tables on other pages of the site (with the same database credentials as I am using on this query).

I found the mysql section of the phpinfo page, but I'm not sure what there might be sensitive info? Here is the types of info it lists, is any of these details I should not post?

mysql
MySQL Support
Active Persistent Links
Active Links
Client API version
MYSQL_MODULE_TYPE
MYSQL_SOCKET
MYSQL_INCLUDE
MYSQL_LIBS

mysql.allow_persistent
mysql.connect_timeout
mysql.default_host
mysql.default_password
mysql.default_port
mysql.default_socket
mysql.default_user
mysql.max_links
mysql.max_persistent
mysql.trace_mode