More single quote/apostrophe problems!

[Beauford]

I have a search form I created to seach a table in my database. When I search it from the form, no problem. The problem is when I save the search in MySQL and then try to retrieve the string and run it.

 

I'm at a loss. When I compare the two search strings (the one from the form and the one from the DB) they are identical, so how can one work and one not?

 

The query is:

 

SELECT userid, username, userlevel, donatordays, gender, level, money, points, house, location, gang, laston FROM users WHERE username LIKE 'Beaufor%'

 

When I echo the string from both the form and the DB  - they are identical.

 

This is what I get when I retieve it from the DB.

 

QUERY ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Beaufor%'' at line 1

 

This is the code I use to get the info - both ways.

 

When inserting into the DB I am using htmlementies, and stripslashes when retrieving it. Have tried mysql_real_escape_string, etc etc. as well.

 

$info=$db->query("{$query}");

$i=$db->num_rows($info);

 

etc.....

 

Any help is appreciated on this, and the whole apostrophe problems as I am still trying to get other similar issues resolved.

 

Hope this makes sense to someone, as I just don't get the whole thing and tried to explain as best as possible.

 

Beauford

 

[Maq]

Can you post the exact portion of code?

 

Why are you putting quotes around your $query variable when it's already a string?

 

$db->query("{$query}");

[redarrow]

Don't need the {like} mysql statement .

 

don't forget to post the search name variable and trim it.

 

<?php
$sql="SELECT userid, username, userlevel, donatordays, gender, level, money, points, house, location, gang, laston FROM users WHERE username = '$search_name'";
?>

 

[Maq]

Don't need the {like} mysql statement .

 

What if he wants all user names that begin with 'Beaufor'?

[redarrow]

 

do it this way then.

<?php
$sql="SELECT userid, username, userlevel, donatordays, gender, level, money, points, house, location, gang, laston FROM users WHERE username LIKE '%$search_name%'";
?>

[Maq]

That still wouldnt' get the desired results if he wanted any username that BEGINS with 'Beaufor'.

 

But I do agree that his query call should look like this:

 

$info=$db->query($query);

[redarrow]

 

this then.

<?php
$sql="SELECT userid, DISTINCT username, userlevel, donatordays, gender, level, money, points, house, location, gang, laston FROM users WHERE username LIKE '%$search_name%'";
?>

[Maq]

Adding DISTINCT doesn't do anything since usernames should already be distinct...

 

Enough with the semantics, his query throws an error, we'll worry about the results later.

[Beauford]

Thanks for all the replies. but I think some of you are missing the problem.

 

If I do the search from the form it works perfectly everytime. If I get the same string from the database, I get the query error I posted.

 

The two strings are identical, so why does one work and one doesn't. This is what I don't get.

 

The only thing I can think of is that the database one has some hidden or unseen characters, null values, line breaks or whatever that the form string doesn't have. Could be wrong, but what else could it be. To the human eye, the two strings are identical.

 

[Beauford]

Update: This works:

 

function mysql_escape($query) {

return str_replace("'","''",$query);

}

[Beauford]

Hi again,

 

My apologies for another post on this, but I am just not getting this at all. Time after time, after time I have problems with apostrophes in any kind of input/output of data when an apostrophe is involved in a string.

 

Example: O'Toole, Ain't that a shame, Fred's Space, etc.

 

This is all I want to do, is input the examples like above, and have them display the same way when I retrieve them from the database.

 

I have tried escaping them everyway I could find, but there is no one solution that seems to work.

 

Can some one give me some kind of definitive rule on this, and speak slow, as I am just at my wits end with this. Right now I just don't allow apostrophes on my site, but this is ridiculous.

 

Thanks

 

B

 

 

[kenrbnsn]

When adding to the database, use mysql_real_escape_string.

When displaying to the screen use htmlentities with the ENT_QUOTES option.

 

Ken

[Beauford]

When adding to the database, use mysql_real_escape_string.

When displaying to the screen use htmlentities with the ENT_QUOTES option.

 

Ken

 

Doesn't work. As I have said, I have tried all these, and same problem.

 

Another problem, related, is when I try to retrieve the data from the table and compare it to an $_POST value, I get a query error as it thinks the apostrophe is the end of the string. Tried escaping all this as well, and same problem.

[Beauford]

When adding to the database, use mysql_real_escape_string.

When displaying to the screen use htmlentities with the ENT_QUOTES option.

 

Ken

 

Doesn't work. As I have said, I have tried all these, and same problem.

 

Another problem, related, is when I try to retrieve the data from the table and compare it to an $_POST value, I get a query error as it thinks the apostrophe is the end of the string. Tried escaping all this as well, and same problem.

 

Just a clarification. If I do your suggestion and display it to the screen, it is fine. If however, I try to select the information from the database and display it back in the form to edit it, I do not want the user to see the apostrophe code, I want them to see the actual apostrophe.

[soak]

Try them again, they definitely do work!

 

If you have problems then post your code snippet and we can advise.

 

Something like:

<?php
$search_name = mysql_real_escape_string($search_name);
$sql="SELECT userid, DISTINCT username, userlevel, donatordays, gender, level, money, points, house, location, gang, laston FROM users WHERE username LIKE '%$search_name%'";
?>

[Beauford]

I guess I'm just not getting the concept here or something. Here is the entire code, as messed up as it is now. So many changes, trying to fix this who knows what's there.

 

Thanks, B

 

function myescape($text) {
  return mysql_real_escape_string($text);
}

function edit_gang() {

global $db,$ir,$gangdata,$privs,$csscode;

if($_SESSION['privs']['Edit_Gang'] == "N") {
	$_SESSION['message'] = "You don't have permission to perform this action!";
	header("Location: gangstaff.php");
	exit();		
}
else {
	if($_POST['submit']) {
		$info=$db->query("SELECT gangNAME, gangPREF FROM gangs");
		while($g=$db->fetch_row($info)) {
			if($_POST['gangNAME'] != $gangdata['gangNAME']) {
				$gn[] = $g['gangNAME'];
			}
			if($_POST['gangPREF'] != $gangdata['gangPREF']) {
				$gp[] = $g['gangPREF'];
			}
		}		
		if(!$_POST['gangNAME']) {
			$formerror['gangNAME'] = "You must enter a gang name!";
		}
		elseif($_POST['gangNAME'] != $gangdata['gangNAME']) {
			$lower = strtolower($_POST['gangNAME']);
			$lower = myescape($lower);
			$reserved=$db->query("SELECT reserved FROM reservednames WHERE reserved='{$lower}'");
			if($db->num_rows($reserved) > 0) {
				$formerror['gangNAME'] = "{$_POST['gangNAME']} is a reserved name!";
			}
			elseif(in_array($_POST['gangNAME'], $gn)) {
				$formerror['gangNAME'] = "{$_POST['gangNAME']} already exists, choose another one!";
			}
		}

		if(!$_POST['gangPREF']) {
			$formerror['gangPREF'] = "You must enter a gang prefix!";
		}			
		elseif($_POST['gangPREF'] != $gangdata['gangPREF']) {
			if(strlen($_POST['gangPREF']) > 2 || strlen($_POST['gangPREF']) < 2) {
				$formerror['gangPREF'] = "The prefix must be 2 characters!";
			}
			elseif(in_array($_POST['gangNAME'], $gp)) {
				$formerror['gangPREF'] = "{$_POST['gangPREF']} already exists, choose another one!";
			}
		}
		if(!$_POST['gangDESC']) {
			$formerror['gangDESC'] = "You must enter a short description!";
		}
		elseif(strlen($_POST['gangDESC']) >= 45) {
			$formerror['gangDESC'] = "You description is too long! (45 characters max)";
		}	
		if(!$_POST['gangLOGO']) {
			$_POST['gangLOGO'] = "http://mafiasyndicate.beauford.ca/images/bankrobber.jpg";
		}

		if(count($formerror) > 0) {	
			$_SESSION['message'] = "There were ".count($formerror)." error(s) in the form. See below for details!";
		}
		else {
			$name=myescape($_POST['gangNAME']);
			$pref=strtoupper(myescape($_POST['gangPREF']));
			$description=myescape($_POST['gangDESC']);
			$logo=myescape($_POST['gangLOGO']);	
			$public=myescape($_POST['gangPUBLIC']);
			$private=myescape($_POST['gangPRIVATE']);

			$db->query("UPDATE gangs SET gangNAME='{$name}', gangPREF='{$pref}', gangDESC='{$description}', gangLOGO='{$logo}', gangPUBLIC='{$public}', gangPRIVATE='{$private}' WHERE gangID='{$gangdata['gangID']}'");
			$_SESSION['message'] = "Your gang preferences have been updated successfully!";

			if($_POST['gangNAME'] != $gangdata['gangNAME']) {
				$db->query("UPDATE forums SET f_NAME='{$name}' WHERE f_GANG={$gangdata['gangID']}");
			}
			header("Location: gangstaff.php");
			exit;	
		}
	}

	if(!$ir['gang']) {
		echo "<h2>Edit Gang</h2><br>
		<div class='grayline'></div>
		<p class='orange'>You're not in a gang fool, you must apply to one first or create your own.</p>
		<div class='grayline'></div>";
	}
	else {
		 if(!$_POST['submit']) {
			foreach($gangdata as $key=>$value) { 
				$_POST[$key] = $value;
			}
		}

		echo "<h2>\"{$_POST['gangNAME']}\" - Edit Gang</h2><br>";
		if(isset($_SESSION['message'])) { echo "<p class='orange'>{$_SESSION['message']}</p><br>"; unset($_SESSION['message']); }					

		echo "
		<p class='p'><b>Note:</b> <i>Change only the fields you want to update. Leave the others as they are.</i></p>
		<form action='gangstaff.php?action=edit' method='post'>
		<input type='hidden' name='submit' value='Submit'>
		<input type='hidden' name='gangPRESIDENT' value='{$_POST['gangPRESIDENT']}'>
		<div class='grayline'></div>	
		<table width=100% cellspacing='1'>
		<tr>		
		<td width='20%'>Gang Name:</td>
		<td width='24%'><input id='input' type='text' name='gangNAME' value='{$_POST['gangNAME']}'></td><td width='56%'>{$formerror['gangNAME']}</td>
		</tr><tr>
		<td>Gang Tag:</td>
		<td><input id='input' type='text' name='gangPREF' value='{$_POST['gangPREF']}'></td><td>{$formerror['gangPREF']}</td>
		</tr><tr>
		<td>Gang Description:</td>
		<td colspan='2'><input id='input' style='width: 434px' type='text' name='gangDESC' value='{$_POST['gangDESC']}' maxlength='45'></td>";
		if($formerror['gangDESC']) { echo "</tr><tr><td width='20%'> </td><td colspan='2'>{$formerror['gangDESC']}</td>"; }
		echo "
		</tr><tr>				
		<td>Gang Logo:</td>
		<td colspan='2'><input id='input' style='width: 434px' type='text' name='gangLOGO' value='{$_POST['gangLOGO']}'></td>
		</tr>
		</table>

		<table width=100% cellspacing='1'>
		<td width='20%'>Public Description:</td>
		<td width='80%' style='padding-left: 2px'><textarea id='textarea' rows='10' cols='50' name='gangPUBLIC'>".$_POST['gangPUBLIC']."</textarea></td>
		</tr><tr>
		<td>Private Description:</td>
		<td style='padding-left: 2px'><textarea id='textarea' rows='10' cols='50' name='gangPRIVATE'>".$_POST['gangPRIVATE']."</textarea></td>
		</tr><tr>
		<td> </td>
		<td> <button class='btn' type='submit' value='submit'>Change</button></td>
		</tr>
		</table>
		</form>
		<div class='grayline'></div>
		<br><p class='p'>Staff Administration</p>";

		StaffMenu();
	}
}
}

[soak]

I've looked through it briefly and I can't see anything obvious. in_array is case sensitive though if that would affect things?

 

Where is it failing when you submit? Do you get one of the error messages? What does it do?

[kenrbnsn]

Why do you use your own function, myescap() -- which just does a mysql_real_escape_string, instead of calling mysql_real_escape_sting directly?

 

I would add

<?php
	if (get_magic_quotes_gpc()) $_POST = array_map('sttipslashes',$_POST);
?>

at the start of the edit_gang() funtion.

 

You aren't using htmlentities when you display the POSTed info.

 

Change things like this:

<?php
value='{$_POST['gangNAME']}'
?>

to

<?php
value='" . htmlentities($_POST['gangNAME'],ENT_QUOTES) . "'
?>

 

Ken

[Beauford]

All the stuff you guys have been saying has been tried at some point in time, but the problem still exists. myescape function - I'm a lazy typer.

 

What I am trying to do is have the user edit his preferences. So I need to have O'Toole, look like O'Toole when the form is populated for the user to edit - not O\'Toole or O#39;Toole (or whatever it is).

 

Here I always get a query error as it thinks the ' in O'Toole is the end of the string.

Tried all sorts of ways to get this to work.

 

$reserved=$db->query("SELECT reserved FROM reservednames WHERE reserved='{$_POST['gangNAME'}'");

if($db->num_rows($reserved) > 0) {

    $formerror['gangNAME'] = "{$_POST['gangNAME']} is a reserved name!";

}

 

Other times, when I go to display it, everything after the ' is gone. so O'Toole would be O. It's correct in the DB, but not on the screen.

 

Thanks for the help, as I am going slightly insane here for something as trivial and stupid as an apostrophe.

 

B

[kenrbnsn]

Run this sample code and you will see how htmlentities works:

<?php
function getval($name) {
$val = (isset($_POST[$name]) && strlen($_POST[$name]) > 0)?'value="' . htmlentities($_POST[$name],ENT_QUOTES) . '"':'';
return($val);
}
if (isset($_POST['submit'])) {
echo '<pre>Before stripslashes: ' . print_r($_POST,true) . '</pre>';
$_POST = array_map('stripslashes',$_POST);
echo '<pre>After stripslashes: ' . print_r($_POST,true) . '</pre>';
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title></title>
</head>
<body>
<form action="" method="post">
Enter string <input type="text" size="30" name="testit" <?php echo getval('testit') ?>><br>
<input type="submit" name="submit" value="Test It">
</form>
</body>
</html>

 

Ken

[Beauford]

Sorry, but just not getting the concept at all.  Can't get my head around this in the least.

 

Just don't get when I am supposed to use what and where to use it.  Then there seems to be different rules for different situations and I end up with screwed up strings.

 

Guess I'll just have to do without apostrophes, too much time wasted on something as stupid as this.

 

Thanks for all the help to those that replied.

 

 

[Beauford]

OK, sorry guys, but I really need to get a handle on this problem. It is popping up in other areas's of my site, as I figured it would.

 

This is the latest error I got, which I believe I have fixed, but would like some input as to whether it is correct or not.

 

QUERY ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Man)', unix_timestamp(), 'lol')' at line 1

Query was INSERT INTO gangmail VALUES('', '9', '87', 'Villie(Tia's Man)', unix_timestamp(), 'lol')

 

This is just a simple announcment type form which enters a message (textarea) into the DB and then is viewed by others.

 

So I have changed it so I have mysql_real_escape_string before inserting the value to the DB, and stripslashes($value) when retrieveing and viewing it.

 

The one that is still causing me nightmares is when I try to populate a form with data from the database. As soon as there is an apostrophe in the string, everything after it does not show up, and I have tried all the stuff that you guys have mentioned, plus a lot more and just can't get it. The dull code was posted earlier.

 

Thanks B.

 

[kenrbnsn]

When displaying strings in forms you MUST use the function htmlentities with the ENT_QUOTES option.

 

Here is your long "echo" statement with the correct coding:

<?php
              echo "
      <p class='p'><b>Note:</b> <i>Change only the fields you want to update. Leave the others as they are.</i></p>
      <form action='gangstaff.php?action=edit' method='post'>
      <input type='hidden' name='submit' value='Submit'>
      <input type='hidden' name='gangPRESIDENT' value='" . htmlentities($_POST['gangPRESIDENT'],ENT_QUOTES) . "'>
      <div class='grayline'></div>  
      <table width=100% cellspacing='1'>
      <tr>    
      <td width='20%'>Gang Name:</td>
      <td width='24%'><input id='input' type='text' name='gangNAME' value='" . htmlentities($_POST['gangNAME'],ENT_QUOTES) . "'></td><td width='56%'>{$formerror['gangNAME']}</td>
      </tr><tr>
      <td>Gang Tag:</td>
      <td><input id='input' type='text' name='gangPREF' value='{$_POST['gangPREF']}'></td><td>{$formerror['gangPREF']}</td>
      </tr><tr>
      <td>Gang Description:</td>
      <td colspan='2'><input id='input' style='width: 434px' type='text' name='gangDESC' value='" . htmlentities($_POST['gangDESC'],ENT_QUOTES) . "' maxlength='45'></td>";
              if ($formerror['gangDESC']) {
                  echo "</tr><tr><td width='20%'> </td><td colspan='2'>{$formerror['gangDESC']}</td>";
              }
              echo "
      </tr><tr>        
      <td>Gang Logo:</td>
      <td colspan='2'><input id='input' style='width: 434px' type='text' name='gangLOGO' value='" . htmlentities($_POST['gangLOGO'],ENT_QUOTES) . "'></td>
      </tr>
      </table>
      
      <table width=100% cellspacing='1'>
      <td width='20%'>Public Description:</td>
      <td width='80%' style='padding-left: 2px'><textarea id='textarea' rows='10' cols='50' name='gangPUBLIC'>" .  htmlentities($_POST['gangPUBLIC'],ENT_QUOTES) . "</textarea></td>
      </tr><tr>
      <td>Private Description:</td>
      <td style='padding-left: 2px'><textarea id='textarea' rows='10' cols='50' name='gangPRIVATE'>" . htmlentities($_POST['gangPRIVATE'],ENT_QUOTES) . "</textarea></td>
      </tr><tr>
      <td> </td>
      <td> <button class='btn' type='submit' value='submit'>Change</button></td>
      </tr>
      </table>
      </form>
      <div class='grayline'></div>
      <br><p class='p'>Staff Administration</p>";
  ?>

 

I don't know how much clearer I can get...

 

Ken

[Beauford]

OK, that seems to do it. Not sure what I was doing the first time. I had the ENT_QUOTES there, but had the same problem. I just and pasted the variables right from your last code this time and it worked.

 

Much appreciated, just one of those things than I completely have a dead spot in the brain about.

 

I also fixed up some other code using this example as well.

 

One last quick question, you made mention of a long echo. Is there any downsides to doing this rather than echoing each line seperately?

 

Thanks again.

 

B

[kenrbnsn]

I think it's all personal preference. I prefer to put all the lines to be sent into an array and then use the implode function to send the output to the screen.  Some people use the heredoc format. Others use long echo statement like yours.

 

Ken