0

I'm working on old website and I found this error in log files:

 Invalid SQL: SELECT COUNT(*) AS color_count FROM colors WHERE id IN (on,on) ;

  mysql 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
  'on,on) ' at line 1

The code php is like that :

$query  = "SELECT COUNT(*) AS color_count FROM colors WHERE id IN ";
$ids    = implode("','", $_GET['id_color']);
$query  .= "('".$ids."') ";

I resolved this error by adding mysql_real_escape_string.

But I want to understand how an SQL injection can modify the query and remove the simple quotes ' from the query?

Improve this question
10
  • 1
    PSA: The mysql_* functions are deprecated in PHP 5.5. It is not recommended for writing new code as it will prevent you from upgrading in the future. Instead, use either MySQLi or PDO and be a better PHP Developer. Commented Apr 24, 2014 at 15:56
  • on,on. It means form has checkbox as input. Am I right? Commented Apr 24, 2014 at 15:56
  • 1
    Are you sure that is the corresponding statement? Commented Apr 24, 2014 at 16:05
  • 1
    Any chance the code you're looking at is not authentic? Any chance the log mangles the quotes? Commented Apr 24, 2014 at 16:05
  • 1
    I can’t imagine a case where the given code would generate the given SQL statement. Are you sure there is not another piece of code that actually generated the given statement? Commented Apr 24, 2014 at 16:15

3 Answers 3

Reset to default
2

SQL injection can only add characters, it cannot remove characters from your SQL string. In other words, it's not "SQL suction". :-)

I can think of these possibilities:

  • The error in the log occurred on a date in the past, before your code did quoting. Perhaps it was originally designed to handle only integers, which aren't required to be quoted.

    I recommend noting the date/time of the error in the log, then retrieve the version of code from your source control corresponding to that date.

  • The error was generated by a similar SQL query in another part of your code, where the code fails to quote the values.

    I recommend searching all of your code for similar SQL queries.

  • Your code (or your framework) strips single-quotes out of the SQL string. I can't guess why it would do this, but in theory it's a possibility.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

+ for addressing the precise question :)
0

SQL injection is a danger (among other cases) anywhere you allow user input to be put directly into the statement. This is why bound statements are more secure and preferred.

The gist of it is, if I can tack on input to the end of your statement, there's nothing to stop me from adding a semicolon to end your current statement, and then a new statement in the same variable. So my string could be:

"11;Drop colors if exists cascade" which a naive execute would execute two statements, one of which completes as you expect, then the malicious one which deletes your table.

Now, a checkbox isn't likely to be a victim of injection, but it should always be a concern.

Do some more research on SQL injection, and really understand it. Then you can start building and modifying code to better combat it.

http://en.wikipedia.org/wiki/SQL_injection

Improve this answer

3 Comments

How does this answer his question?
It asked how sql injection could modify his specific query. It can if there's any way for the user to tack on something to his query. Its a checkbox so this isn't likely, however, it still explains the dangers. Without knowing more of the code, its possible for things like someone inserting a querystring to set id_color to a string.
No, the actual question is: “How with sql injection can modify the query and remove the simple Quotes ' from the query?”.
0

It can be ' or '1'='1 if you want to break single quotes (http://en.wikipedia.org/wiki/SQL_injection).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.