Jump to content
Larry Ullman's Book Forums

Mysqli_Real_Escape_String - Query Problem


Recommended Posts

Hello forum members,

 

I am using mysqli_real_escape_string but when I run the query with the escaped variable, the query no longer works.

 

The queries below include pseudo-code.

$name = "larry's forum";
$escaped_name = mysqli_real_escape_string($dbc, $name);
$q = "SELECT ... WHERE name = '$escaped_name' ";

The printed query looks like this:

SELECT ... WHERE ... name = 'Larry's forum'

The apostrophe appears to be the problem. What am I doing wrong?

 

 

Thanks in advance!!!

Link to comment
Share on other sites

Hi Larry,

 

I think the problem lies in my running the variable through FILTER_SANITIZE_STRING before I use mysqli_real_escape_string. The apostrophe in 'Larry's forum' turns into '.

 

Do you think I should thus forgo using filter_input or use something else in place of it?

 

Thank you for helping me!

$sanitized_name = filter_input(INPUT_GET, 'name', FILTER_SANITIZE_STRING);
$escaped_name = mysqli_real_escape_string($dbc, $sanitized_name);
$q = "SELECT ... WHERE name = '$escaped_name' ";
Link to comment
Share on other sites

Yeah, that kind of doesn't make sense. If FILTER_SANITIZE_STRING is turning the apostrophe into its entity version, then there's no apostrophe to be escaped by mysqli_real_escape_string() and there's no un-escaped apostrophe to break the query.

Link to comment
Share on other sites

Yeah, that kind of doesn't make sense. If FILTER_SANITIZE_STRING is turning the apostrophe into its entity version, then there's no apostrophe to be escaped by mysqli_real_escape_string() and there's no un-escaped apostrophe to break the query.

 

 

 

Yes, so that means I shouldn't use FILTER_SANITIZE_STRING before using mysqli_real_escape_string()? I need to have the apostrophe in the string.

 

Thank you!

Link to comment
Share on other sites

Hi HartleySan,
 

 

 

Apostrophes in the string are not going to cause mysqli_real_escape_string to break.

 

 

No, but if I first use FILTER_SANITIZE_STRING then there's no more apostrophe in the string, since it gets converted to '. I'll have to skip FILTER_SANITIZE_STRING and use mysqli_real_escape_string on its own. Am I Right?

 

Thanks.

Link to comment
Share on other sites

No, you're not. The actual characters in the string should be completely irrelevant.

 

The whole point of mysqli_real_escape_string is to escape characters that could potentially harm a DB query. That's it.

The actual characters in the string should not cause the function to fail.

 

Are you sure your DB connection and all of that is valid?

Link to comment
Share on other sites

Ok, let's forget about mysqli_real_escape_string for the moment. If I use FILTER_SANITIZE_STRING the apostrophe becomes '.

 

Having a query such as

$q = "SELECT ... WHERE name = 'larry's forum' ";

doesn't work. At least not when I tried it in phpmyadmin. I need the apostrophe in the query.

Link to comment
Share on other sites

The query you've presented is syntactically fine. Helping you debug this would go A LOT faster if you were to present an actual query being executed (as I've suggested before) and an actual error message (as HartleySan just suggested). As it stands, you're presenting theoretical code that should work and complaining about problems with actual code not working.

Link to comment
Share on other sites

Ok, the query below in phpmyadmin returns no rows:

SELECT f.forum_name FROM forums AS f WHERE f.forum_name = 'larry's forum'

The message is: MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0012 sec )

 

 

This query works fine:

SELECT f.forum_name FROM forums AS f WHERE f.forum_name = 'larry\'s forum' 

Thank you!

Link to comment
Share on other sites

Thanks for providing actual examples. This makes perfect sense, then. You've stored the values WITH the escaped apostrophe, so if you want to do a search, you cannot apply FILTER_SANITIZE_STRING to the search terms. 

 

In short, you should apply the same functions/techniques on INSERT as you do on SELECT...WHERE.

 

Also, to be clear, I thought you meant "returned an error" when you say the query wasn't working (presumably HartleySan thought the same). Your queries are working (i.e., they're not broken), they just aren't returning any records. 

Link to comment
Share on other sites

Interesting problem. I've followed this one from the sideline. My programming teacher always said logical bugs are the hardest to debug, and I would have to agree. I was really interested in seeing whether there was a bug in mysqli_real_escape_string or not. 

Link to comment
Share on other sites

 Share

×
×
  • Create New...