[RESOLVED] Problem with special characters in data

[plainhat]

What would I do without you folks! I hope I learn enough so I can eventually pay it forward. But in the meantime...

OK here's the problem:

I'm creating a dynamic select list that ends up pulling items from the data base that contain an apostrophe (as in the word "Alzheimer's").

so php generates the following html:

<option value='aging'>aging</option>
<option value='Alzheimer's'>Alzheimer's</option>
<option value='antacids'>antacids</option> 
....

Of course when "Alzheimer's" is sent as a variable it mucks up the works and destroys the query I'm sending it to.

Now I know if I was to create the option manually, I'd escape the ' with a slash. Is there a way to escape it programatically as I'm pulling it from the database to populate the select list?

Or do I have to change the stuff in the data base so that it pre-escaped?

(I'm feeling like maybe I have a touch of Alzheimer's) :queasy:

[rikmoncur]

have a look at

addslashes()
stripslashes()
mysql_real_escape_string()

[dagon]

double quote you're html attributes, and apply mysql_real_escape_string() to the variables being inserted in to the db.

[bradgrafelman]

As dagon pointed out, make sure you're sanitizing user-supplied data (all user-supplied data, at that!) when INSERT'ing it into the database.

As for outputting data from a SQL db into an HTML document, I actually have a different suggestion than those above. In this case, I personally would run the SQL-supplied data through [man]htmlspecialchars/man before placing it into HTML code such as an OPTION element to ensure that I don't inadvertently break the HTML's syntax while using external data.

[plainhat]

Thanks to you all folks.
This may take a while (and be assured when I sort out my problem I'll resolve the thread). I'm experimenting with all of this in between learning (or trying to learn) tons of other stuff at lightning speed.

rikmoncur Thanks for the quick answer!

dagon, You picked up on the sign of how clueless I am. Your point about double quoting my html attributes is well taken. I would ordinarily double quote html attributes when writing it by hand, but wasn't thinking about how the single quote in my php would go bad when it encountered the single quote in the variable. Of course the easy answer is to escape the double quotes in my php not the single quote in Alzheimer's.. doh!

Now that the form is passing the whole word with the apostrophe I'm off to figure out how to manage it when it gets passed to the form processor and the db. So far I keep getting mysql errors when trying to use mysql_real_escape_string(). Though a connection is there and working, it complains that the function has been denied access. Obviously, I need to do some more figuring out on that one.

bradgrafelman THEN I will see what htmlspecialchars() can do for me. While checking data in this instance is not particularly important, it would be nicer not to worry about the odd character slipping in (not to mention I am aware of the problems of code injection and such and will want to be sure to learn enough to protect the data before sending anything into production).

I can't tell you all how much I appreciate you putting up with this newbie.

[plainhat]

OK, now comes the problem with mysql_real_escape_string()

This is the warning I get

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'SYSTEM'@'localhost' (using password: NO)

I quickly realized that I'm not connected to the db using SYSTEM so I changed the privileges table in mysql to include SYSTEM and tried creating a connection using that user and password before calling the method.

No go.

I'm going to google it some more, but any help would be appreciated.

[NogDog]

You need to do your mysql_connect() before using mysql_real_escape_string().

[plainhat]

Yes, I did. I ran the call for the connection on the line before I called the mysql_real_escape_string() function. I even tested it by dropping a query on the same page. The query runs and I get a response. It is only the mysql_real_escape_string() that throws the error.

[bradgrafelman]

Can you show us the code that includes the call to mysql_connect (don't forget to remove your DB credentials) as well as where you're trying to escape data?

[dagon]

you are using mysql and not maybe mysli? I don't see you posting what db or Extension you are using.

[plainhat]

Dagon, you were so right. I added a connection to mysql and the error vanished. HOWEVER, I'm still getting an error when passing the word with a single quote/apostrophe to the query. My guess is that I don't clearly understand how the mysql_real_escape_string function is supposed to work.

I guess there's nothing left but to show off my wimpy code with the error. It works perfectly unless the word passed to the query has an apostrophe.

Error:

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\show_arts_key.php on line 15

Code:

$key_req = $_POST['key_req']; 
// mysql connection
require_once('Connections/sqlnoi.php');
mysql_real_escape_string($key_req);
// mysqli connection
require_once('Connections/local.php');
$query_keys = "SELECT titles.title, titles.year, keywords.keyword
	FROM keywords INNER JOIN (titles INNER JOIN keywordarticle
	ON titles.articleID=keywordarticle.articleID
	AND keywordarticle.keywordID = '$key_req')
	ON keywords.keyword=keywordarticle.keywordID";
$result = mysqli_query ($cxn,$query_keys);
echo "<h2>" . $key_req ."</h2>\n<ul>";
while($row = mysqli_fetch_assoc($result)){
	$title = $row['title'];
	echo "<li>".$title."</li>";
}
echo "</ul>"

[plainhat]

Big stupid me... I got it working.

New code:

// mysql connection
require_once('Connections/sqlnoi.php');
$escapekey = mysql_real_escape_string($key_req);
// mysqli connection
require_once('Connections/local.php');
$query_keys = "SELECT titles.title, titles.year, keywords.keyword
FROM keywords INNER JOIN (titles INNER JOIN keywordarticle
		ON titles.articleID=keywordarticle.articleID
		AND keywordarticle.keywordID = '$escapekey')
		ON keywords.keyword=keywordarticle.keywordID";
$result = mysqli_query ($cxn,$query_keys);
echo "<h2>" . $key_req ."</h2>\n<ul>";
while($row = mysqli_fetch_assoc($result)){
	$title = $row['title'];
	echo "<li>".$title."</li>";
}
echo "</ul>"

THANKS ALL

[bradgrafelman]

If you're using the MySQLi library, you should be using [man]mysqli_real_escape_string/man...

[plainhat]

Thanks... yep... I did finally figure that out. So finally:

$key_req = $_POST['key_req']; 
require_once('Connections/local.php');
$key_req = mysqli_real_escape_string($cxn,$key_req);
$query_keys = "SELECT titles.title, titles.year, keywords.keyword
		FROM keywords INNER JOIN (titles INNER JOIN keywordarticle
		ON titles.articleID=keywordarticle.articleID
		AND keywordarticle.keywordID = '$key_req')
		ON keywords.keyword=keywordarticle.keywordID";
$result = mysqli_query ($cxn,$query_keys);
echo "<h2>" . stripslashes($key_req) ."</h2>\n<ul>";
while($row = mysqli_fetch_assoc($result)){
		$title = $row['title'];
		echo "<li>".$title."</li>";
}
echo "</ul>"

Again. Thanks to all of you. I'll probably be stumbling in here a bit as I feel my way around.