1

So using %27 you can just SQL inject even though data is sanitized with mysql_real_escape_string 

%27) SQL INJECTION HERE %2F*

What to do?

Edit with example:

$sql = sprintf("SELECT *, MATCH(post) AGAINST ('%s*' IN BOOLEAN MODE) AS score FROM Posts WHERE MATCH(post) AGAINST('%s*' IN BOOLEAN MODE)",
                mysql_real_escape_string($_GET['searchterm']),
                mysql_real_escape_string($_GET['searchterm']));

$results = $db->queryAsArray($sql);

If you pass in %27) SQL INJECTION HERE %2F* to the searchterm querystring, I get outputted on the page:

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 'BOOLEAN MODE)' at line 1

Thanks everyone for finding the problem in the db class..

Improve this question
15
  • 7
    ?? Please show a full real world example of this. Commented Mar 14, 2011 at 20:58
  • 1
    possible duplicate of Is mysql_real_escape_string() broken? Commented Mar 14, 2011 at 20:58
  • 1
    This is not a duplicate of Is mysql_real_escape_string() broken? Commented Mar 14, 2011 at 21:01
  • 1
    @Igor K: Please post the resulting query, i.e. the value of $sql. And what does queryAsArray do? Commented Mar 14, 2011 at 21:13
  • 3
    Wait – are you using this Db class? Commented Mar 14, 2011 at 21:15

4 Answers 4

Reset to default
2

Reasoning from the method name queryAsArray, it seems that you’re using this DbBase class from the comments of the MySQL functions manual page. If so, it’s the query method that removes the escape character from the escaped quotation marks:

function query($sql, &$records = null){
    $sql = str_replace(array('\\"', "\\'"), array('"', "'"), $sql);
    // …
}

Then it’s not a miracle that your example works (I simplified it):

$input = "', BAD SQL INJECTION --";

$sql = "SELECT '".mysql_real_escape_string($input)."'";
var_dump($sql);  // string(33) "SELECT '\', BAD SQL INJECTION --'"
//                      everything’s OK ↑

$sql = str_replace(array('\\"', "\\'"), array('"', "'"), $sql);
var_dump($sql);  // string(32) "SELECT '', BAD SQL INJECTION --'"
//                                Oops! ↑
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

The note mentioned in our manual has been marked for deletion. Once it propagates across all of the mirrors in our network, it will no longer appear attached to the official documentation.

~ Daniel P. Brown
  Network Infrastructure Manager
  http://php.net/
Improve this answer

Comments

0

It's best to not to build statements like this at all, and instead use queries with parameters using mysqli or PDO. This will deal with the problem of MySQL injection and one day (not yet, unfortunately) it will perform better too, because the queries are cached without parameters, meaning you only got one query in the cache instead of dozens of different queries because of a single input value changing all the time. Other databases make use of this since long, but MySQL just managed not to make parameterized queries slower since the latest version.

It doesn't look plausible that %27 will actually terminate the string. It seems more like a possibility to embed quotes inside a string, but I'm not sure.

To be sure, I decided to sacrificed my server and test this. When I enter %27 in an input field and textarea that are escaped using mysql_real_escape_string and are then inserted in the database, I get no errors. The text %27 is just inserted. So no problem at all.

Improve this answer

13 Comments

It makes me wonder why everyone who rants about parameterized queries, never brings an example with them. May be it's because the code would be more complex and messy with no real benefits?
he didn't mean literal %27 but an apostrophe coming from query string.
Sure, but in PHP you got either an apostrof that is escaped by mysql_real_escape_string or you got the literal text %27 which apparently doesn't need to be escaped. So why the downvote? Point is you can't inject SQL this way. The question suggested that an url-escaped quote would pass mysql_real_escape_string and would cause trouble in the SQL statement. But it won't unless you're using it in a LIKE. :p
it's just empty blab again and no working example. That's what I am talking about
The only one who's ranting is you. I already explained about the nuances of the 'better performance' statement, but could you please tell us why they are not more secure? The only thing you've essentially said is "No, not true", but without explanation, motivation or example. I've taken the effort to take my code, translate it and put it online. You can take a look at a real life example here: www.eftelist.nl. Now please come with a useful reply or just sod off and go compile yourself.
|
-1

You are wrong. No injection possible here.

By following these three simple rules

  1. Client's encoding properly set by mysql_set_charset()
  2. Data being escaped using mysql_real_escape_string()
  3. And enclosed in quotes

you can be sure that no injection possible

Improve this answer

2 Comments

@Igor Let's see. But I am sure you are under impression of some mistake. Urlencoding (%27) has nothing to do with database. With proper syntax no injection possible
I assure you that is the exact code I'm running and yet it seems I have SQL injection

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.