Wagtail Posted May 6, 2014 Share Posted May 6, 2014 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 More sharing options...
Larry Posted May 8, 2014 Share Posted May 8, 2014 That would suggest that mysqli_real_escape_string() isn't working, which is odd. I'd verify what's going on and share your actual code. Link to comment Share on other sites More sharing options...
Wagtail Posted May 10, 2014 Author Share Posted May 10, 2014 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 More sharing options...
HartleySan Posted May 10, 2014 Share Posted May 10, 2014 I'm not sure why that would mess up mysqli_real_escape_string, but maybe. Please let us know if you figure out what's wrong. Thanks. Link to comment Share on other sites More sharing options...
Larry Posted May 11, 2014 Share Posted May 11, 2014 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 More sharing options...
Wagtail Posted May 12, 2014 Author Share Posted May 12, 2014 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 More sharing options...
HartleySan Posted May 12, 2014 Share Posted May 12, 2014 Apostrophes in the string are not going to cause mysqli_real_escape_string to break. Something else is going on. Link to comment Share on other sites More sharing options...
Wagtail Posted May 12, 2014 Author Share Posted May 12, 2014 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 More sharing options...
HartleySan Posted May 12, 2014 Share Posted May 12, 2014 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 More sharing options...
Wagtail Posted May 12, 2014 Author Share Posted May 12, 2014 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 More sharing options...
HartleySan Posted May 12, 2014 Share Posted May 12, 2014 What's the error message? If you change the name string to something more standard, does it go through okay? Link to comment Share on other sites More sharing options...
Larry Posted May 12, 2014 Share Posted May 12, 2014 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 More sharing options...
Wagtail Posted May 12, 2014 Author Share Posted May 12, 2014 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 More sharing options...
Larry Posted May 12, 2014 Share Posted May 12, 2014 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 More sharing options...
HartleySan Posted May 13, 2014 Share Posted May 13, 2014 Yes, Larry's 100% right. I thought there was an error, but there is not. You're getting an empty set because the string 'larry's forum' does not exist in the forum_name column, which is perfectly understandable. Link to comment Share on other sites More sharing options...
Wagtail Posted May 13, 2014 Author Share Posted May 13, 2014 Hi Larry and HartleySan, thank you for assisting me with this and I really do appreciate the help. It's all working fine now. Link to comment Share on other sites More sharing options...
HartleySan Posted May 13, 2014 Share Posted May 13, 2014 You're welcome. Please note though that in the future, you can save us a lot of headaches by giving us all the information we need from the very start. Thanks. 1 Link to comment Share on other sites More sharing options...
Antonio Conte Posted May 14, 2014 Share Posted May 14, 2014 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 More sharing options...
Recommended Posts