Annoying PHP SQL problem

m_tCh

Hi,
I'm sure this is an easy question for one of you to answer. I can't figure it out. Why can I not get my database to accept words with apostrophes in them like "Don't" or "Can't". Whenever you enter a word with an apostrophy and submit, you get this error:

Database error - the webmaster has been notified.

There was an error on a dynamic website page.

Error: You have an error in your SQL syntax near 'dsf')' at line 1
SQL: INSERT INTO PROJECT_LIST (REQUEST_DATE,REQUESTOR,CUSTOMER,SCOPE) VALUES ('asdf','asdf','SFM',''dsf')
User: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)

My code is attached. I really appreciate the help! Thanks so much in advance!

LordShryku

Look at the SQL code in the error.
...VALUES ('asdf','asdf','SFM',''dsf')
You can see how it would be confused. [man]addlslashes[/man] to the values before executing it.

m_tCh

I'm an idoit. I still can't get it to work. I know I'm phrasing it wrong. Any quick ideas. Here is my failed attempts:

//$strid = $_GET["id"];

//$date = $_GET["date1"];

include "functions.php";

require("phpmailer/class.phpmailer.php");

if ($_SERVER["REQUEST_METHOD"] == "POST") {

$mailto = "--";

$mailto2 = "--";

$mailto3 = "--";

$xREQUEST_DATE = checkString($_POST["REQUEST_DATE"],"REQUEST_DATE",1,32);

$xREQUESTOR = checkString($_POST["REQUESTOR"],"REQUESTOR",1,100);

$xTITLE = checkString($_POST["TITLE"],"TITLE",1,100);

$xCUSTOMER = checkString($_POST["CUSTOMER"],"CUSTOMER",1,100);

$xPHONE = checkString($_POST["PHONE"],"PHONE",0,32);   

$xEMAIL = checkString($_POST["EMAIL"],"EMAIL",1,100);

$xSCOPE = checkString($_POST["SCOPE"],"SCOPE",1,5000);

$xQ2 = checkString($_POST["Q2"],"Q2",1,5000);

$xQ3 = checkString($_POST["Q3"],"Q3",0,5000);

$xQ4 = checkString($_POST["Q4"],"Q4",0,5000);   

$xregion = checkString($_POST["region"],"Region",1,100);

if ($errStr == '')  {


	$xmessagecontent = "Request Date:\t$xREQUEST_DATE\n" .

		"Requestor:\t $xREQUESTOR\n" .

		"Customer:\t $xCUSTOMER\n" .

		"Title:\t $xTITLE\n" .

		"Region:\t $xregion\n" .

		"Phone:\t $xPHONE\n" .

		"Email:\t $xEMAIL\n" .

		"Question 1:\t $xSCOPE\n" .

		"Question 2:\t $xQ2\n" .

		"Question 3:\t $xQ3\n" .

		"Question 4:\t $xQ4\n" ;

	$xREQUEST_DATE=addslashes($_POST[REQUEST_DATE]); 
	$xREQUESTOR=addslashes($_POST[REQUESTOR]); 
	$xTITLE =addslashes($_POST[TITLE]); 
	$xCUSTOMER =addslashes($_POST[CUSTOMER]); 
	$xPHONE=addslashes($_POST[PHONE]); 
	$xEMAIL=addslashes($_POST[EMAIL]); 
	$xSCOPE =addslashes($_POST[SCOPE]); 
	$xQ2=addslashes($_POST[Q2]); 
	$xQ3=addslashes($_POST[Q3]); 
	$xQ4=addslashes($_POST[Q4]); 
	$xregion=addslashes($_POST[region]); 		


	$sql = "INSERT INTO PROJECT_LIST (REQUEST_DATE,REQUESTOR,CUSTOMER,SCOPE) VALUES " .

			"(" . escapeSQL($xREQUEST_DATE) . "," . escapeSQL($xREQUESTOR) . "," . escapeSQL($xCUSTOMER). "," . escapeSQL($xSCOPE) . ")";

			$strid = mysql_insert_id();

			execSQL($sql);

......

m_tCh

anyone...I'm really stumped. I would greatly appreciate it!

liquorvicar

first, try using the PHP tags on the forum. it makes it easier for people to read.

second, you have

 $sql = "INSERT INTO PROJECT_LIST (REQUEST_DATE,REQUESTOR,CUSTOMER,SCOPE) VALUES " .

"(" . escapeSQL($xREQUEST_DATE) . "," . escapeSQL($xREQUESTOR) . "," . escapeSQL($xCUSTOMER). "," . escapeSQL($xSCOPE) . ")";

$strid = mysql_insert_id();

execSQL($sql);

i don't know what your 'escapeSQL' function does but i suspect it's doing the same as 'addslashes'.
also, you have mysql_insert_id(); before execSQL($sql);. mysql_insert_id returns the ID of the last insert. if you haven't executed the query there won't be an ID.

try, adding die($sql); above execSQL($sql); and posting the output back here.

m_tCh

There was an error on a dynamic website page.

Error: You have an error in your SQL syntax near 'gh')' at line 1
SQL: INSERT INTO PROJECT_LIST (REQUEST_DATE,REQUESTOR,CUSTOMER,SCOPE) VALUES ('1999-07-06','1999-07-06','SCBNA','sdf'gh')
User: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)

It still has something to do with with me not coding the addslashes correctly. Because it works fine either way as long as you don't just any apostrophes in the fields that get inserted into the database.

Anyone know how to help?

liquorvicar

what's your escapeSQL function doing? it may be undoing the addslashes. can you post it here?

also what happens when you echo out $xSCOPE right before your query line?