[Resolved] Apostrophy ' in text field problem

[m_tCh]

Hi,
I have a problem with some code I am working on. It works correctly to update text fields in a database except for when I have a word with an apostrophy in it like "Fred's". I get a syntax error:

You have an error in your SQL syntax near 's Cygnet site needs to be overhauled for two primary reasons: 1) To update with ' at line 1

What do I have to do to the code to get it to accept apostrophy's in the text fields?

Thanks a ton for your help!

---

whiteboard_showmodrecord.php code snippit

<?

	$passed_vars = array_merge($_POST,$_GET);

	$sql = "SELECT * FROM PROJECT_LIST WHERE ID = '$passed_vars[sel_record]'";

	$sql_result = mysql_query($sql) or die (mysql_error());
	if (!$sql_result) {
		echo "Something has gone wrong!";
	} else {

		while ($record = mysql_fetch_array($sql_result)) {
			$id = $record['ID'];
			$project_name = stripslashes($record['PROJECT_NAME']);
			$category = stripslashes($record['CATEGORY']);
			$customer = stripslashes($record['CUSTOMER']);
			$requestor = stripslashes($record['REQUESTOR']);
			$responder = stripslashes($record['RESPONDER']);
			$request_date = stripslashes($record['REQUEST_DATE']);
			$response_date = stripslashes($record['RESPONSE_DATE']);
			$dev = stripslashes($record['REQ_DELIVERY_DATE']);
			$scope = stripslashes($record['SCOPE']);
			$support = stripslashes($record['SUPPORT_NEEDED']);
			$est = stripslashes($record['DEVEL_EST']);
			$planned_start = stripslashes($record['PLANNED_START']);
			$actual_start = stripslashes($record['ACTUAL_START']);
			$planned_complete = stripslashes($record['PLANNED_COMPLETE']);
			$actual_complete = stripslashes($record['ACTUAL_COMPLETE']);
			$designer1 = stripslashes($record['DESIGNER1']);
			$designer2 = stripslashes($record['DESIGNER2']);
			$designer3 = stripslashes($record['DESIGNER3']);
			$designer4 = stripslashes($record['DESIGNER4']);
			$status = stripslashes($record['STATUS']);
		}
   		echo "


		<FORM method=\"POST\" action=\"whiteboard_domodrecord.php\">
		<INPUT TYPE=\"hidden\" name=\"id\" value=\"$id\">
		<p align=center><INPUT type=\"submit\" value=\"Modify Project\"></p>
		<P><strong>Project Name</strong>
		<INPUT type=\"text\" name=\"project_name\" value=\"$project_name\"
		size=100 maxlength=100>
		<P><strong>Project Status</strong>
		<SELECT name=\"category\">
		<OPTION value=\"\">-- Select One --</OPTION>";
		$cat_array = array("Requested", "Active", "Complete", "Cancelled");
		foreach ($cat_array as $cat) {
			if ($cat == "$category") {
				echo "<OPTION value=\"$cat\" selected>$cat</OPTION>";
			} else {
				echo "<OPTION value=\"$cat\">$cat</OPTION>";
			}
		}

		echo "</SELECT>


		<P><strong>Customer:</strong> 
					<SELECT name=\"customer\">
		<OPTION value=\"\">-- Select One --</OPTION>";
		$cat1_array = array("TSFC", "SCBNA", "SFM", "FS", "HS", "SHARED", "OTHER");
		foreach ($cat1_array as $cat1) {
			if ($cat1 == "$customer") {
				echo "<OPTION value=\"$cat1\" selected>$cat1</OPTION>";
			} else {
				echo "<OPTION value=\"$cat1\">$cat1</OPTION>";
			}
		}

		echo "</SELECT>


		<P><strong>Requestor:</strong> <INPUT type=\"text\" name=\"requestor\" value=\"$requestor\"  size=25 maxlength=25>
		<P><strong>Request Date (yyyy-mm-dd):</strong> <INPUT type=\"text\" name=\"request_date\" value=\"$request_date\"  size=12 maxlength=12>
		<P><strong>Response Date (yyyy-mm-dd):</strong> <INPUT type=\"text\" name=\"response_date\" value=\"$response_date\"  size=12 maxlength=12>
		<P><strong>Project Manager:</strong> <INPUT type=\"text\" name=\"responder\" value=\"$responder\"  size=25 maxlength=25>
		<P><strong>Requested Delivery Date (yyyy-mm-dd):</strong> <INPUT type=\"text\" name=\"dev\"  value=\"$dev\" size=15 maxlength=15><br>
		<P><strong>Project Scope:</strong><br>
		<TEXTAREA name=\"scope\" cols=70 rows=8>$scope</TEXTAREA>
		<P><strong>Development Estimate:</strong> <INPUT type=\"text\" name=\"est\"  value=\"$est\" size=15 maxlength=15><br>
		<P><strong>Planned Start Date (yyyy-mm-dd):</strong> <INPUT type=\"text\" name=\"planned_start\"  value=\"$planned_start\" size=15 maxlength=15><br>
		<P><strong>Actual Start Date (yyyy-mm-dd):</strong> <INPUT type=\"text\" name=\"actual_start\"  value=\"$actual_start\" size=15 maxlength=15><br>
		<P><strong>Planned Completion Date (yyyy-mm-dd):</strong> <INPUT type=\"text\" name=\"planned_complete\"  value=\"$planned_complete\" size=15 maxlength=15><br>
		<P><strong>Actual Completion Date (yyyy-mm-dd):</strong> <INPUT type=\"text\" name=\"actual_complete\"  value=\"$actual_complete\" size=15 maxlength=15><br>
		<P><strong>Team Member 1:</strong> <INPUT type=\"text\" name=\"designer1\"  value=\"$designer1\" size=30 maxlength=30><br>
		<P><strong>Team Member 2:</strong> <INPUT type=\"text\" name=\"designer2\"  value=\"$designer2\" size=30 maxlength=30><br>
		<P><strong>Team Member 3:</strong> <INPUT type=\"text\" name=\"designer3\"  value=\"$designer3\" size=30 maxlength=30><br>
		<P><strong>Team Member 4:</strong> <INPUT type=\"text\" name=\"designer4\"  value=\"$designer4\" size=30 maxlength=30><br>
		<p align=center><INPUT type=\"submit\" value=\"Modify Project\"></p>
		</FORM>
		";
		}

?>

---

whiteboard_domodrecord.php

---

<?

	$conn = mysql_connect() or die(mysql_error());

	$db = mysql_select_db() or die(mysql_error());

	$sql = "UPDATE PROJECT_LIST SET PROJECT_NAME = '$_POST[project_name]', CATEGORY = '$_POST[category]', CUSTOMER = '$_POST[customer]', REQUEST_DATE = '$_POST[request_date]', RESPONSE_DATE = '$_POST[response_date]', RESPONDER = '$_POST[responder]', SCOPE = '$_POST[scope]', SUPPORT_NEEDED = '$_POST[support]', REQUESTOR = '$_POST[requestor]', REQ_DELIVERY_DATE = '$_POST[dev]', PLANNED_START = '$_POST[planned_start]', ACTUAL_START = '$_POST[actual_start]', PLANNED_COMPLETE = '$_POST[planned_complete]', ACTUAL_COMPLETE = '$_POST[actual_complete]', DESIGNER1 = '$_POST[designer1]', DESIGNER2 = '$_POST[designer2]', DESIGNER3 = '$_POST[designer3]', DESIGNER4 = '$_POST[designer4]', STATUS = '$_POST[status]',  DEVEL_EST = '$_POST[est]' WHERE ID = '$_POST[id]'";
	$result = mysql_query($sql) or die (mysql_error());

	if (isset($result)) {
		echo "<HTML>
		<HEAD>
		<TITLE>Modify a Project</TITLE>
		</HEAD>
		<BODY>
		<h1>The new record looks like this:</h1>
		<P><strong>Project Name:</strong> ".stripslashes($_POST['project_name'])."
		<P><strong>Category:</strong> ".stripslashes($_POST['category'])."
		<P><strong>Requestor:</strong> ".stripslashes($_POST['requestor'])."
		<P><strong>Customer:</strong> ".stripslashes($_POST['customer'])."
		<P><strong>Request Date:</strong> ".stripslashes($_POST['request_date'])."
		<P><strong>Response Date:</strong> ".stripslashes($_POST['response_date'])."
		<P><strong>Project Manager:</strong> ".stripslashes($_POST['responder'])."
		<P><strong>Requested Develivery Date:</strong> ".stripslashes($_POST['dev'])."
		<P><strong>Development Estimate:</strong> ".stripslashes($_POST['est'])."
		<P><strong>Planned Start:</strong> ".stripslashes($_POST['planned_start'])."
		<P><strong>Actual Start:</strong> ".stripslashes($_POST['actual_start'])."
		<P><strong>Planned Completion:</strong> ".stripslashes($_POST['planned_complete'])."
		<P><strong>Actual Completion:</strong> ".stripslashes($_POST['actual_complete'])."
		<P><strong>Designer 1:</strong> ".stripslashes($_POST['designer1'])."
		<P><strong>Designer 2:</strong> ".stripslashes($_POST['designer2'])."
		<P><strong>Designer 3:</strong> ".stripslashes($_POST['designer3'])."
		<P><strong>Designer 4:</strong> ".stripslashes($_POST['designer4'])."
		<P><strong>Status:</strong> ".stripslashes($_POST['status'])."
		<P><strong>Scope:</strong> ".stripslashes($_POST['scope'])."



		<P align=center><a href=\"default.htm\">Return to Menu</a></p>
		</BODY>
		</HTML>";
	} else {
		echo "Some sort of error has occurred.</p>";
	}

?>

[bubblocity]

You want AddSlashes() function applied to your Text fields before doing update in query.

But this also depends on your MAGIC QUOTES setting in php configuration (in php.ini?) ( I don't know what the default is anymore)

If magic quotes is ON, then when fields are submitted through a form by method=post, then the server automatically adds a slash for you, so you can just use it in a query. In this case, if you use the variable for something else, like to send email, you will have to remember to strip the slashes.

Conversely, if MAGIC QUOTES is off, you have to addslashes to your text data before putting the datain query.

$DB[project_name]=addslashes($POST[project_name]);
$DB[category]=addslashes($POST[category]);
$DB[id] = $_POST[id];

$sql = "UPDATE PROJECT_LIST
SET PROJECT_NAME = '$DB[project_name]',
CATEGORY = '$DB[category]'
WHERE ID = '$DB[id]'";

Something like this...

[bubblenut]

For each field being inserted into the table you need to call either [man]addslashes[/man] or [man]mysql_escape_string[/man] on it to escape any characters which may cause problems. This will also help prevent SQL injection attacks.

[m_tCh]

see correctly pasted code in my next post

[Roger_Ramjet]

Yep, write some clean code. Assuming version 4.3 or above, otherwise use mysql_escape_string

// test for magic quotes first and do this if they are OFF 
$var1 = mysql_real_escape_string($_POST['project_name']);
$var2 = mysql_real_escape_string($_POST['category']);

// etc

// else do this if they are ON
$var1 = $_POST['project_name']; 
$var2 = $_POST['category'];

$query = "UPDATE PROJECT_LIST SET
      PROJECT_NAME = '$var1',
      CATEGORY = '$var2',"

// etc

Shortcuts in code are boobytraps waiting to happen, and poorly structured code is a pain to debug.

[bubblocity]

This is the only line I see changed? Are you saying this change caused it to erase?

$db = mysql_select_db("", $conn) or die(mysql_error());

How were you testing? Did you just reload the page from the URL? And did you go back and complete the form and hit submit again? It's hard to tell, since your code does not explicitly check for a Submit button click (or something of that nature)

[bubblocity]

Also, here's a function I used to use to apply to all my POST form data.

It makes a copy of everything in $POST array, and puts it in $DB as an array, applying addslashes() to every variable in the Array. You could of course change it to mysql_escape_string(). Then you can just use $DB inside your query instead of $POST.

editinputArray($DB, $_POST);

$sql = "UPDATE PROJECT_LIST 
       SET PROJECT_NAME = '$DB[project_name],
       DEVEL_EST = '$DB[est]' 
      WHERE ID = '$DB[id] ";




function editinputarray(&$ArrayOut, $ArrayIn) {
	if(is_array($ArrayIn)){
		while(list($key,$value)=each($ArrayIn)){
			if(is_array($ArrayIn[$key])){
				editinputarray($ArrayOut[$key], $ArrayIn[$key]);
			}
			else{
				if (get_magic_quotes_gpc()==1) {
				    $ArrayOut[$key] = trim($value);
				  } else {
				    $ArrayOut[$key]= AddSlashes(trim($value));
	  			}
			}
		}//while
	}//if
	return;
}

[m_tCh]

I copied and pasted wrong. Here is what I should have posted.

updated code based on suggestions that erases:

$DB[project_name]=addslashes($POST[project_name]);
$DB[category]=addslashes($POST[category]);
$DB[customer]=addslashes($POST[customer]);
$DB[request_date]=addslashes($POST[request_date]);
$DB[response_date]=addslashes($POST[response_date]);
$DB[responder]=addslashes($POST[responder]);
$DB[scope]=addslashes($POST[scope]);
$DB[support]=addslashes($POST[support]);
$DB[requestor]=addslashes($POST[requestor]);
$DB[dev]=addslashes($POST[dev]);
$DB[planned_start]=addslashes($POST[planned_start]);
$DB[actual_start]=addslashes($POST[actual_start]);
$DB[planned_complete]=addslashes($POST[planned_complete]);
$DB[actual_complete]=addslashes($POST[actual_complete]);
$DB[designer1]=addslashes($POST[designer1]);
$DB[designer2]=addslashes($POST[designer2]);
$DB[designer3]=addslashes($POST[designer3]);
$DB[designer4]=addslashes($POST[designer4]);
$DB[status]=addslashes($POST[status]);
$DB[est]=addslashes($POST[est]);
$DB[id] = $_POST[id];

	$sql = "UPDATE PROJECT_LIST 
	SET PROJECT_NAME = '$DB[project_name]', 
	CATEGORY = '$DB[category]',
	CUSTOMER = '$_DB[customer]',
	REQUEST_DATE = '$_DB[request_date]',
	RESPONSE_DATE = '$_DB[response_date]',
	RESPONDER = '$_POST[responder]',
	SCOPE = '$_DB[scope]',
	SUPPORT_NEEDED = '$_DB[support]',
	REQUESTOR = '$_DB[requestor]',
	REQ_DELIVERY_DATE = '$_DB[dev]', 
	PLANNED_START = '$_DB[planned_start]',
	ACTUAL_START = '$_DB[actual_start]', 
	PLANNED_COMPLETE = '$_DB[planned_complete]',
	ACTUAL_COMPLETE = '$_DB[actual_complete]', 
	DESIGNER1 = '$_DB[designer1]',
	DESIGNER2 = '$_DB[designer2]',
	DESIGNER3 = '$_DB[designer3]',
	DESIGNER4 = '$_DB[designer4]',
	STATUS = '$_DB[status]', 
	DEVEL_EST = '$_DB[est]'
	WHERE ID = '$DB[id]'"; 

	$result = mysql_query($sql) or die (mysql_error());

	if (isset($result)) {
		echo "<HTML>
		<HEAD>
		<TITLE>Modify a Project</TITLE>
		</HEAD>
		<BODY>
		<h1>The new record looks like this:</h1>
		<P><strong>Project Name:</strong> ".stripslashes($_POST['project_name'])."
		<P><strong>Category:</strong> ".stripslashes($_POST['category'])."
		<P><strong>Requestor:</strong> ".stripslashes($_POST['requestor'])."
		<P><strong>Customer:</strong> ".stripslashes($_POST['customer'])."
		<P><strong>Request Date:</strong> ".stripslashes($_POST['request_date'])."
		<P><strong>Response Date:</strong> ".stripslashes($_POST['response_date'])."
		<P><strong>Project Manager:</strong> ".stripslashes($_POST['responder'])."
		<P><strong>Requested Develivery Date:</strong> ".stripslashes($_POST['dev'])."
		<P><strong>Development Estimate:</strong> ".stripslashes($_POST['est'])."
		<P><strong>Planned Start:</strong> ".stripslashes($_POST['planned_start'])."
		<P><strong>Actual Start:</strong> ".stripslashes($_POST['actual_start'])."
		<P><strong>Planned Completion:</strong> ".stripslashes($_POST['planned_complete'])."
		<P><strong>Actual Completion:</strong> ".stripslashes($_POST['actual_complete'])."
		<P><strong>Designer 1:</strong> ".stripslashes($_POST['designer1'])."
		<P><strong>Designer 2:</strong> ".stripslashes($_POST['designer2'])."
		<P><strong>Designer 3:</strong> ".stripslashes($_POST['designer3'])."
		<P><strong>Designer 4:</strong> ".stripslashes($_POST['designer4'])."
		<P><strong>Status:</strong> ".stripslashes($_POST['status'])."
		<P><strong>Scope:</strong> ".stripslashes($_POST['scope'])."



		<P align=center><a href=\"default.htm\">Return to Menu</a></p>
		</BODY>
		</HTML>";
	} else {
		echo "Some sort of error has occurred.</p>";
	}

?>

original code:

$sql = "UPDATE PROJECT_LIST SET PROJECT_NAME = '$POST[project_name]',
CATEGORY = '$POST[category]',
CUSTOMER = '$POST[customer]',
REQUEST_DATE = '$POST[request_date]',
RESPONSE_DATE = '$POST[response_date]',
RESPONDER = '$POST[responder]',
SCOPE = '$POST[scope]',
SUPPORT_NEEDED = '$POST[support]',
REQUESTOR = '$POST[requestor]',
REQ_DELIVERY_DATE = '$POST[dev]',
PLANNED_START = '$POST[planned_start]',
ACTUAL_START = '$POST[actual_start]',
PLANNED_COMPLETE = '$POST[planned_complete]',
ACTUAL_COMPLETE = '$POST[actual_complete]',
DESIGNER1 = '$POST[designer1]',
DESIGNER2 = '$POST[designer2]',
DESIGNER3 = '$POST[designer3]',
DESIGNER4 = '$POST[designer4]',
STATUS = '$POST[status]',

DEVEL_EST = '$POST[est]' WHERE ID = '$_POST[id]'";

$result = mysql_query($sql) or die (mysql_error());

	if (isset($result)) {
		echo "<HTML>
		<HEAD>
		<TITLE>Modify a Project</TITLE>
		</HEAD>
		<BODY>
		<h1>The new record looks like this:</h1>
		<P><strong>Project Name:</strong> ".stripslashes($_POST['project_name'])."
		<P><strong>Category:</strong> ".stripslashes($_POST['category'])."
		<P><strong>Requestor:</strong> ".stripslashes($_POST['requestor'])."
		<P><strong>Customer:</strong> ".stripslashes($_POST['customer'])."
		<P><strong>Request Date:</strong> ".stripslashes($_POST['request_date'])."
		<P><strong>Response Date:</strong> ".stripslashes($_POST['response_date'])."
		<P><strong>Project Manager:</strong> ".stripslashes($_POST['responder'])."
		<P><strong>Requested Develivery Date:</strong> ".stripslashes($_POST['dev'])."
		<P><strong>Development Estimate:</strong> ".stripslashes($_POST['est'])."
		<P><strong>Planned Start:</strong> ".stripslashes($_POST['planned_start'])."
		<P><strong>Actual Start:</strong> ".stripslashes($_POST['actual_start'])."
		<P><strong>Planned Completion:</strong> ".stripslashes($_POST['planned_complete'])."
		<P><strong>Actual Completion:</strong> ".stripslashes($_POST['actual_complete'])."
		<P><strong>Designer 1:</strong> ".stripslashes($_POST['designer1'])."
		<P><strong>Designer 2:</strong> ".stripslashes($_POST['designer2'])."
		<P><strong>Designer 3:</strong> ".stripslashes($_POST['designer3'])."
		<P><strong>Designer 4:</strong> ".stripslashes($_POST['designer4'])."
		<P><strong>Status:</strong> ".stripslashes($_POST['status'])."
		<P><strong>Scope:</strong> ".stripslashes($_POST['scope'])."



		<P align=center><a href=\"default.htm\">Return to Menu</a></p>
		</BODY>
		</HTML>";
	} else {
		echo "Some sort of error has occurred.</p>";
	}

[bubblocity]

OK, I see it.

up top everything says $DB (when applying addslashes())

In the query sometimes, it says $_DB