标签云

微信群

扫码加入我们

WeChat QR Code

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:$unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')What can be done to prevent this from happening?


Just to add because I didn't see it anywhere else here, another line of defense is a web application firewall (WAF) that can have rules be set to look for sql injection attacks:

2019年05月24日12分21秒

Also, the official documentation of mysql_query only allows to execute one query, so any other query besides ; is ignored. Even if this is already deprecated there are a lot of systems under PHP 5.5.0 and that may use this function. php.net/manual/en/function.mysql-query.php

2019年05月24日12分21秒

This is a bad habit but is a post-problem solution : Not only for SQL injection but for any type of injections (for example there was a view template injection hole in F3 framework v2) if you have a ready old website or app is suffering from injection defects , one solution is to reassign the values of your supperglobal predefined vars like $_POST with escaped values at bootstrap. By PDO, still it is possible to escape (also for today frameworks) : substr($pdo->quote($str, \PDO::PARAM_STR), 1, -1)

2019年05月24日12分21秒

This answer lacks the explanation of what is a prepared statement - one thing - it's a performance hit if you use a lot of prepared statements during your request and sometimes it accounts for 10x performance hit. Better case would be use PDO with parameter binding off, but statement preparation off.

2019年05月24日12分21秒

Using PDO is better, in case you are using direct query make sure you use mysqli::escape_string

2019年05月24日12分21秒

using mysql_real_escape_string is enough or i must use parameterized too?

2019年05月24日12分21秒

peimanF. keep a good practice of using parametrized queries, even on a local project. With parametrized queries you are guaranteed that there will not be SQL injection. But keep in mind you should sanitize the data to avoid bogus retrieval (i.e. XSS injection, such as putting HTML code in a text) with htmlentities for example

2019年05月24日12分21秒

peimanF. Good practise to parametrized queries and bind values, but real escape string is good for now

2019年05月24日12分21秒

I understand the inclusion of mysql_real_escape_string() for completeness, but am not a fan of listing the most error-prone approach first. The reader might just quickly grab the first example. Good thing it's deprecated now :)

2019年05月24日12分21秒

Great, well thought out article.I might add that using the Sanitize filters of PHP is kind of (but not exactly) a white listing of sorts.For example, FILTER_SANITIZE_NUMBER_INT only allows number characters, thereby white listing characters, not entire strings.In combination with prepared statements, it makes a good "belt and suspenders" approach.

2019年05月24日12分21秒

Sablefoste you don't need whitelisting here. Any sanitization will be redundant. Less rules to follow, the less mistakes you will make. Although you could do any validations, do it for sake of your application logic, but not for database.

2019年05月24日12分21秒

This is the best answer.

2019年05月24日12分21秒

doesn't PDO wrap mysqli for MySQL DBs? In which case surely it can't be any quicker than mysqli. I'd still recommend it though. It's a much better interface that the mysqli API.

2019年05月24日12分21秒

Using parameterized queries is what speeds up the queries. Technically mysqli might be even faster by a very small margin.The actual amount of time the server takes to respond the the query eclipses any difference in timing that might happen because you are using a wrapper.But mysqli is tied to the database.If you want to use a different database engine, you have to change all the calls that use mysqli.Not so for PDO.

2019年05月24日12分21秒

PDO doesn't support dynamic order by unfortunately :(

2019年05月24日12分21秒

From wikipedia: Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

2019年05月24日12分21秒

SumitGupta Yea, you did. MySQL doesnt concatenate with + but with CONCAT. And to the performance: I dont think it affects performance because mysql has to parse data and it doesnt matter if origin is string or hex

2019年05月24日12分21秒

YourCommonSense What errors do you encounter? Be specific.

2019年05月24日12分21秒

YourCommonSense You dont understand the concept... If you want to have string in mysql you quote it like this 'root' or you can hex it 0x726f6f74 BUT if you want a number and send it as string you will probably write '42' not CHAR(42) ... '42' in hex would be 0x3432 not 0x42

2019年05月24日12分21秒

YourCommonSense I have nothing to say... just lol... if you still want to try hex on numeric fields, see second comment. I bet with you that it'll work.

2019年05月24日12分21秒

YourCommonSense you still dont understand ? You cannot use 0x and concat because if the string is empty you will end with an error. If you want simple alternative to your query try this one SELECT title FROM article WHERE id = UNHEX(' . bin2hex($_GET["id"]) . ')

2019年05月24日12分21秒

This is the best you can do with legacy mysql extension. For new code, you're advised to switch to mysqli or PDO.

2019年05月24日12分21秒

I am not agree with this 'a specially-made function to prevent these attacks'. I think that mysql_real_escape_string purpose is in allow to build correct SQL query for every input data-string. Prevention sql-injection is the side-effect of this function.

2019年05月24日12分21秒

you dont use functions to write correct input data-strings.You just write correct ones that don't need escaping or have already been escaped.mysql_real_escape_string() may have been designed with the purpose you mention in mind, but its only value is preventing injection.

2019年05月24日12分21秒

WARNING!mysql_real_escape_string() is not infallible.

2019年05月24日12分21秒

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed in the future from PHP.Its best to move onto what the PHP or MySQL folks recommend.

2019年05月24日12分21秒

I have tried your example and it's work fine for me.Could you clear "this won't solve every problem"

2019年05月24日12分21秒

If you don't quote the string, it's still injectable. Take $q = "SELECT col FROM tbl WHERE x = $safe_var"; for example. Setting $safe_var to 1 UNION SELECT password FROM users works in this case because of the lack of quotes. It's also possible to inject strings into the query using CONCAT and CHR.

2019年05月25日12分21秒

Polynomial Completely right, but I'd see this merely as wrong usage. As long as you use it correctly, it will definitely work.

2019年05月25日12分21秒

WARNING!mysql_real_escape_string() is not infallible.

2019年05月24日12分21秒

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed in the future from PHP.Its best to move onto what the PHP or MySQL folks recommend.

2019年05月24日12分21秒

Indeed; running with magic_quotes switched on just encourages poor practice. However, sometimes you can't always control the environment to that level - either you don't have access to manage the server, or your application has to coexist with applications that (shudder) depend on such configuration. For these reasons, it's good to write portable applications - though obviously the effort is wasted if you do control the deployment environment, e.g. because it's an in-house application, or only going to be used in your specific environment.

2019年05月24日12分21秒

As of PHP 5.4, the abomination known as 'magic quotes' has been killed dead. And good riddance to bad rubbish.

2019年05月24日12分21秒

You can prevent SQL injection if you adopt an input validation technique in which user input is authenticated against a set of defined rules for length, type and syntax and also against business rules.

2019年05月24日12分21秒

I think your first paragraph is important. Understanding is key. Also, everyone is not working for a company. For a large swath of people, frameworks actually go against the idea of understanding. Getting intimate with the fundamentals may not be valued while working under a deadline, but the do-it-yourselfers out there enjoy getting their hands dirty. Framework developers are not so privileged that everyone else must bow and assume they never make mistakes. The power to make decisions is still important. Who is to say that my framework won't displace some other scheme in the future?

2019年05月24日12分21秒

AnthonyRutledge You are absolutely correct. It is very important to understand what is going on and why. However, the chance that a true-and-tried and actively used and developed framework has run into and solved a lot of issues and patched a lot of security holes already is pretty high. It's a good idea to look at the source to get a feel for the code quality. If it's an untested mess it's probably not secure.

2019年05月24日12分21秒

Here. Here. Good points. However, would you agree that many people can study and learn to adopt an MVC system, but not everyone can reproduce it by hand (controllers and server). One can go too far with this point. Do I need to understand my microwave before I heat up my peanut butter pecan cookies my girl friend made me?;-)

2019年05月24日12分21秒

AnthonyRutledge I agree! I think the use-case makes a difference too: Am I building a photo gallery for my personal homepage or am I building an online banking web application? In the latter case it's very important to understand the details of security and how a framework that I am using is addressing those.

2019年05月24日12分21秒

Ah, the security exception to the do it yourself corollary. See, I tend to be willing to risk it all and go for broke. :-) Kidding. With enough time, people can learn to make a pretty darn secure application. Too many people are in a rush. They throw their hands up and assume that the frameworks are safer. After all, they do not have enough time to test and figure things out. Moreover, security is a field that requires dedicated study. It is not something mere programmers know in depth by virtue of understanding algorithms and design patterns.

2019年05月24日12分21秒

This is one of the few cases where I would use an "escaped value" instead of a prepared statement.And integer type conversion is extremely efficient.

2019年05月24日12分21秒

The question is very generic. Some great answers above, but most suggest prepared statements. MySQLi async does not support prepared statements, so the sprintf looks like a great option for this situation.

2019年05月24日12分21秒

This answer is essentially wrong, as it doesn't help to prevent an injection prevention but just trying to soften the consequences. In vain.

2019年05月24日12分21秒

Right, it doesn't provide a solution, but is what you can do before hand to avoid things.

2019年05月24日12分21秒

Apurv If my goal is to read private information from your database, then not having the DELETE permission means nothing.

2019年05月24日12分21秒

AlexHolsgrove: Take it easy, I was just suggesting good practices for softening the consequences.

2019年05月25日12分21秒

Apurv You don't want to "soften consequences", you want to do everything possible to protect against it. To be fair though, setting the correct user access is important, but not really what the OP is asking for.

2019年05月24日12分21秒

I think the whole point of the question is to get this done without using such framework.

2019年05月24日12分21秒

Please explain oci_bind_by_name parameters.

2019年05月24日12分21秒

Also, there is absolutely no point in checking $_POST array members with is_string()

2019年05月24日12分21秒

WARNING!mysql_real_escape_string() is not infallible.

2019年05月24日12分21秒

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed from PHP in the future.Its best to move onto what the PHP or MySQL folks recommend.

2019年05月24日12分21秒

Theme: Do not trust user's submitted data. Anything you expect is a garbage data with special characters or boolean logic, which should itself become a part of SQL query you may be executing. Keep $_POST values as data only, not SQL part.

2019年05月24日12分21秒