SQL injection with union and load_file()

M开发者_如何学Pythony website has been attacked by SQL injection. Hacker used following in URL query string:

abc-buy.php?sid=144760&op=-3+union+all+select+1,2,3,4,5,6,7,load_file%28%22/etc/passwd%22%29

How can I avoid these kind of attacks?

1. Always validate untrusted input.
2. All input is untrusted.

How to validate the input depends on what the input is, but in this case, it's probably pretty obvious that -3+union+all+select+1,2,3,4,5,6,7,load_file%28%22/etc/passwd%22%29 is not valid input for op (whatever op is).

So in this case, it would probably be as simple as adding some code to check that the value for "op" matches one of the expected values.

if ( op != "or" and op != "and" and op != "monkeys" ) {
    raise_exception("Invalid op specified! Go away you trickster!");
}

You should do this for every value you receive from users. Although it's trickier for free-form fields, like email addresses or comments, etc. But still, make sure they are valid data for the field they're matching--and escape any free-form fields before you insert them into the database. That can make the difference between:

INSERT INTO users (username,fullname) VALUES ("bob","Robert"); DROP TABLE users; SELECT 1 WHERE "x"="");

and:

INSERT INTO users(username,fullname) VALUES ("bob",Robert\"\)\; DROP TABLE users\; SELECT 1 WHERE \"x\"=\"");

The functional difference being that with the first (un-escaped) version, the DROP TABLE users; command executes, and with the second, you simply insert a new user with a really long, silly name of Robert"); DROP TABLE users; SELECT 1 WHERE "x"=".

Switch to PDO and use prepared statements with placeholders for everything.

As most of the answers says, you should escape everything you save into your database (field placeholders).

But I have recently discovered that you should escape all place holders in your query, because without it:

Placeholders for the "FROM clause" could allow hackers to access any table's data.

Placeholders for the "WHERE clause", could allow hackers to any row in the current table. That means a hacker could access your app as any user in your database when trying to log in.

use zend framework. that will by default prevent it http://framework.zend.com/

or everything you put in the database you escape.

http://php.net/manual/en/function.mysql-real-escape-string.php