Problem with the word "from"

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
danrl
Forum Newbie
Posts: 6
Joined: Sat Jan 27, 2007 6:14 pm

Problem with the word "from"

Post by danrl »

I am in the process of building a large php/mysql form to collect information for an insurance company to quote car insurance premiums.

I have all of the form working except for a single very strange problem. Whenever the word "from" or the phrase "INSERT INTO" is placed in the comments field it results in the following error being reported on a 403 error page:

Forbidden
You don't have permission to access /Equote/quoteform.php on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

The 403 error I understand, the web server expects a custom error page that I have not created.

Here is the sql statement that appears to cause the problem:

$sql = "INSERT INTO $table_Q(quote_date, name, comment) VALUES('$date1','$name','$comment');";

I say appears because if the comments section does not have these words or phrases in it, the statement correctly inserts the data into the database.

I believe I have eliminated the problem being the database by using the following line:

$sql = "INSERT INTO $table_Q(comment) VALUES('$comment');";

in a test page that writes to the same database and use of the word "from" does not cause a problem and the database is properly updated. However the prasee "INSERT INTO" does cause the same problem.

Another developer I discussed this problem with suggested the problem was that "from" was a keyword. If that is the case then what list of keywords is causing the problem.

The server is using:

Linux 2.4.32-ow1
MySQL 4.0.27-standard
PHP 4.3.11
Apache 1.3.33 (Unix)

Thanks for your help in advance.

-- Dan
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post by louie35 »

I have a database with more the 15000 records and mostly use the the word from as "from 00-06" and never got an error.

Also it seems you have an extra ";" in your insert code:

Code: Select all

$sql = "INSERT INTO $table_Q(quote_date, name, comment) VALUES('$date1','$name','$comment');"; 
// should be

$sql = "INSERT INTO $table_Q(quote_date, name, comment) VALUES('$date1','$name','$comment')";
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Does that matter through php?
Semicolon after SQL Statements?

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

Some SQL tutorials end each SQL statement with a semicolon. Is this necessary? We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but some database programs force you to use it.
danrl
Forum Newbie
Posts: 6
Joined: Sat Jan 27, 2007 6:14 pm

Is semicolon necessary?

Post by danrl »

I have always used the semicolon in my sql statements within php, basically from habit. When working in MySQL command line the semicolon is required, but when in php they are optional.

But just to make sure, I did remove the semicolon from the statement:

Code: Select all

Changed from:
$sql = "INSERT INTO $table_Q(quote_date, name, comment) VALUES('$date1','$name','$comment');"; 

To:
$sql = "INSERT INTO $table_Q(quote_date, name, comment) VALUES('$date1','$name','$comment')";
And it made no difference.

Thanks for the comment. I am willing to try anything.

Could this problem be caused by something in the Apache config file?

Thanks
-- Dan
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post by louie35 »

try to do an insert using the following code and give use the echo statement here to see:

Code: Select all

$sql = "INSERT INTO $table_Q(quote_date, name, comment) VALUES('$date1','$name','$comment')"; 
echo $sql;//give us the part that will seen on the screen by this echo
die();
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Maybe, just maybe, inputting the variables directly into the statement is causing your problem. Personally, it's always been a thing of mine not to do so (mostly because it's more noticeable to the eye if there's a problem), but maybe it makes a difference.

Code: Select all

$sql = "INSERT INTO $table_Q(quote_date, name, comment) VALUES('".$date1."','".$name."','".$comment."');";
If this doesn't do it, it's very likely that you have an error elsewhere that's causing this.
danrl
Forum Newbie
Posts: 6
Joined: Sat Jan 27, 2007 6:14 pm

Post by danrl »

louie35,

I installed your code changes and actually already had the echo statement but not the die(). The result works fine when the comment does not contain the word "from".
INSERT INTO quotes(quote_date, name, comment) VALUES('2007-01-28','Jimmy','This is a test comment')
However if I use the word "from" in the comment the same error occurs.
Forbidden
You don't have permission to access /Equote/quoteform.php on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.
I hope this helps, I am at a loss. The only reason I am focusing on this particular line of code is it seems to be the only one the can be changed and have an effect on the error.

Thanks
-- Dan
danrl
Forum Newbie
Posts: 6
Joined: Sat Jan 27, 2007 6:14 pm

Post by danrl »

superdezign,

I entered your code into the file and again if the word "from" is not used it works just fine.
INSERT INTO quotes(quote_date, name, comment) VALUES('2007-01-28','Johny','This is a test message');
However if I use the word "from" in the comment section the same error occurs.

Thanks for your help,

-- Dan
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

I'm sure whatever is happening, once you find it you'll say "Ohhhhhh." Maybe there's a problem with how you access your database, or your database itself. Maybe somehow, it's too sensitive?

I've never had a problem with that, so I'm lost as to what to tell you. It has gotta be something beyond that one line of code.
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post by louie35 »

it seems that the error gets there from somwhere else.

try this:

Code: Select all

// Field comment
	$theValue = (!get_magic_quotes_gpc()) ? addslashes($comment) : $comment; 
	$theValue = ($theValue != "") ? " '" . $theValue . "'" : "NULL";
	$fieldList["`comment`"] = $theValue;

	// Field quote_date
	$theValue = ($date1 != "") ? " '" . ConvertDateToMysqlFormat($date1) . "'" :  "'" . date("D, d M Y H:i:s") . "'";
	$fieldList["`quote_date`"] = $theValue;

	// Field name
	$theValue = (!get_magic_quotes_gpc()) ? addslashes($name) : $name; 
	$theValue = ($theValue != "") ? " '" . $theValue . "'" : "NULL";
	$fieldList["`name`"] = $theValue;

	// Insert
	$sSql = "INSERT INTO $table_Q (";
	$sSql .= implode(",", array_keys($fieldList));
	$sSql .= ") VALUES (";
	$sSql .= implode(",", array_values($fieldList));
	$sSql .= ")";

               echo $sSql; //comment this if it show properly
               die(); //comment this if it show properly
                // my sql insert action below
danrl
Forum Newbie
Posts: 6
Joined: Sat Jan 27, 2007 6:14 pm

Post by danrl »

I inserted your latest code and it ended with an error.
Fatal error: Call to undefined function: convertdatetomysqlformat()
Is this a php 5 function?

I commented out the 2 lines of code dealing with the date function and inserted

Code: Select all

$fieldList["`quote_date`"] = $date1;
The output of the new code resulted in the following:
INSERT INTO quotes (`comment`,`quote_date`,`name`) VALUES ( 'test quote',2007-01-28, 'Jonny')
I ran the code a second time with "from" in the comments and ended up with the same original error.

Sorry but no joy from this code either.

I really appreciate your help and work on this.

Thanks
--- Dan
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post by louie35 »

the insert shouldn't happen if you have the die(); uncommented.
the page will just write your $sSql on the screen. Can you copy and paste the $sSql here?

also there is the function missing. sorry i forgot to gove it to you:

Code: Select all

define("DEFAULT_DATE_FORMAT", "dd/mm/yyyy");
define("EW_DATE_SEPARATOR","/");
// Convert a date to MySQL format
function ConvertDateToMysqlFormat($dateStr)
{
	@list($datePt, $timePt) = explode(" ", $dateStr);
	$arDatePt = explode(EW_DATE_SEPARATOR, $datePt);
	if (count($arDatePt) == 3) {
		switch (DEFAULT_DATE_FORMAT) {
		case "yyyy" . EW_DATE_SEPARATOR . "mm" . EW_DATE_SEPARATOR . "dd":
		    list($year, $month, $day) = $arDatePt;
		    break;
		case "mm" . EW_DATE_SEPARATOR . "dd" . EW_DATE_SEPARATOR . "yyyy":
		    list($month, $day, $year) = $arDatePt;
		    break;
		case "dd" . EW_DATE_SEPARATOR . "mm" . EW_DATE_SEPARATOR . "yyyy":
		    list($day, $month, $year) = $arDatePt;
		    break;
		}
		return trim($year . "-" . $month . "-" . $day . " " . $timePt);
	} else {
		return $dateStr;
	}
}
danrl
Forum Newbie
Posts: 6
Joined: Sat Jan 27, 2007 6:14 pm

Post by danrl »

louie35

Thanks for your help on this. I really appreciate it.

I inserted the date function and the last code you gave me without anything commented out. This time your date function worked fine.

When I entered data into the form without the word "from" the form worked as it is supposed to and here is the output:
INSERT INTO quotes (`comment`,`quote_date`,`name`) VALUES ( 'Just a test message', '2007-01-28', 'Freddy')
However when "from" was included in the comments the original error message is returned.
Forbidden
You don't have permission to access /Equote/quoteform.php on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.
Again thanks for your help. At this time please do not spend anymore time on this. Since I have incorporated your changes and suggestions I am convinced the problem does not reside in the sql. I am going to go back to the form designer and talk to him about some design changes that may be affecting this problem. He currently wants a review page to follow the form page. So the flow is:

1. Fill out form and click Submit
2. Present data on form with no input fields. Agent reviews data for mistakes. If a mistake is present or a change is needed then click back button. If all is OK then click Save.
3. Now the data is written to the DB.

I am going to re-write the form without Step 2. So the data is saved when the user clicks Submit. I do not know if the extra page is causing the problem or not, but I am going to get rid of it. This is the first time anyone has ever asked me to use a "review" page before the data was saved.

Thanks again for your efforts. As soon as I know something I will let you know.

-- Dan
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

danrl, post your code.

Also consider

Code: Select all

INSERT INTO `$table_Q`(quo...
instead of

Code: Select all

INSERT INTO $table_Q(quo...
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

ole wrote:danrl, post your code.

Also consider

Code: Select all

INSERT INTO `$table_Q`(quo...
instead of

Code: Select all

INSERT INTO $table_Q(quo...

As much as I would like to agree that would help, i can't.



Secondly, I would try echo'ing out your sql statement, and then manually run it on the command line (or phpMyAdmin, or MySQL QUery Browser, or whatever you use).

See if the query works. If it does, you have an issue outside of your query. If it doesn't, check to see if it's breaking out of the apostraphe or double-quote tags. Maybe all you need is addslashes ;)
Post Reply