I have to watch every word I say when talking to a critical programmer eh
HEREâS MY REVISED CHECKLIST ABOUT PREVENTING SQL INJECTION: (This is not for anyoneâs benefit but mine, and anyone else who might read this thread trying to learn the difference between magic quotes, mysql_real_escape_string, and prepared statements.)
First, understand that âescapingâ characters tells the SQL statement that the character being escaped is to be used in a literal sense. By escaping \âquotes\â with the \ backslash, those quotes will be interpreted as literal textual quotes rather than quotes used in forming a query. This is important so that things like quotes do not allow the attacker to expand or change the intent of a query.
mysql_real_escape_string() is one escaping method that is very safe, as long as one validates all other variable types properly (eg. ensure integer variables actually contain integer data).
prepared statements are a different method that is very safe because it is not required to escape data before executing the query. PDO prepared statements take care of this for you. variable data is separated from the query by being bound as data to a prewritten SQL statement so that the intent of the query cannot be changed by an attacker.
there is no sense in using BOTH the mysql_real_escape_string() method or the prepared statement method for extra protection, because they both do the same thing but in different ways. using mysql_real_escape_string() is a very MANUAL method, so the programmer must remember to properly escape/validate every bit of data before the query is executed. using prepared statements is a bit more AUTOMATIC, and also allows for a faster running experience when a specific query needs to be executed over and over (because the statement only has to be âpreparedâ once). also, because you do not have to worry about escaping your data, using prepared statements has a pretty huge advantage over doing things âmanuallyâ with mysql_real_escape_string().
even if you are using prepared statements, be careful about LIMIT operators where there is 2 arguments because quote escaping will not sanitize them; neither method above would not be safe.
even if you are using prepared statements, always make sure identifiers (table names, field names) and operators (AND, OR) are hard coded into your query and not dynamically entered via variable.
âŚif you guys have anything to add/change in my notes about SQL injection above, please let me know what i am missing or where i went wrong.
I have to watch every word I say when talking to a critical programmer eh
May be I have to be less critical.
But it is again matter of understanding, of which I am big fan
I believe that a man, who knows ABC can invent any word, unlike of a man, who knows only certain words to use.
So, my further comments are of it:
Correction for [1]: This is important because there can be special characters in the text and it will produce an error. Additionally, if user input involved, this do not allow the attacker to expand or change the intent of a query.
Correction for [3]: prepared statements are a different method that is very safe because it cannot be mixed with anything else (e.g. input filtering) and one cannot forget to bind a variable because it will produce immediate error.
[5] is totally wrong. Youâre too inattentive. Thereâs an example at the top of this page shows that prepared statements are all right with LIMIT.
Ahh ok. I realized that about the above example by hash, which is why I wrote âbe carefulâ when using LIMIT⌠you are right though, I was unlcear by saying that they ARE unsafe. I should have said they COULD be unsafe. Howâs this:
even if you are using prepared statements, be careful about LIMIT operators where there is 2 arguments. you can do this again be validating or setting variable types. in the case of using LIMIT in a query, ensure the variable values are bound as INT dataâŚ
Even though prepared statements are safer, there is still a danger in that the PHP code is providing the SQL statements to be used. It can be tempting to dynamically generate SQL statements depending on different situations.
A safer option than using prepared statements is o use stored procedures, where the SQL code resides within the database itself, where the only assumption then is that the stored procedure is properly written.
SQL Attacks By Example has a lot of information that should helpto round out our knowledge, about what makes attacks possible, how theyâre made, and what can be done about it.
All these articles are just toilet paper.
All started from one assumption: âLets assume that programmer is an idiot and donât sanitize input. Now we can inject this way⌠another wayâŚblah blah blahâ
Remove that assumption, and your your paper will remain blank.
Theyâre no good for you because you already know the information. Itâs a good thing that youâre not their intended audience. We want to get the information to people who do not already know it.
You didnât get the point.
This âinformationâ costs nothing. Because it can help nothing. One simple rule can eliminate all these scaring tales. Especially because it utilizes one single vulnerability.
The op already knows the rule. Whatâs the use of this article for him?
Not all information has to be for immediate practical application. I want to know why I should write code a certain way just as much as a mechanic wants to know how a car engine heâs building works, not just that he must put oil in it before running it.
If you do not know the reasons behind certain practices then how can you be confident they are the best practice?
It certainly helped me to attack my own systems where other people in the team had done a less-than stellar job. It was a really good wake-up call to them, and helped to reinforce the importance of maintaining standards.
If you havenât seen any articles that cover the topic well enough, you really should think about writing up something yourself, that people can use as reference material.
Thanks for the link pmw57. As for the article being âtoilet paperâ, I can see how reading about injection attacks can be useful. As he mentioned, itâs not intended for you as an audience because you already know whatâs possible with SQL injection. You take your knowledge for granted.
I myself did NOT understand how someone could modify my SQL when it was being processed by PHP; a server side language! âŚwhat do you mean someone can âinjectâ sql into my statement? You would have had a hard time explaining to me why escaping quotes is so important if you are using mysql_query() and simple strings. I did not understand any of this until I came across one of these articles. It showed me by example how someone could inject into a login form to bypass it altogether. Did that ever scare me! âŚAnd by seeing the example, now I know HOW it is possible and WHY escaping quotes is important. I am still learning, but itâs easier to see the whole picture when I can see how the attack works, and how the defending function works against it.
You guys have had a hard enough time trying to get these concepts into my thick head. Despite your frustration shrapnel, Iâve continued to press on asking question after question because I really need to understand WHY each method works. Like you said to me earlier shrapnelâŚ
And you were right about me being in danger. I was still trying to grasp the difference between literal quotes and unescaped quotes! Like Dan said, a mechanic needs to know how an engine works. I would hope that if my car broke down, my mechanic could tell me what went wrong and why his method works the best, rather than âI donât know, I was just told to use that oil.â
Again, no disrespect intended. I appreciate your help. But donât look down on me for asking questions and trying to do my research by reading other articles
I have another question for you guys. I was reading this article from mysql.com on SQL injection, and I picked up on an interesting concept not mentioned in this thread yetâŚ
âDatabase Permissions - The last database security tip has nothing to do with PHP per se, but is sound advice that can be applied to every component in your system. In general, grant the fewest privileges possible.
For example, if a user only requires read-access to the database, donât permit the user to execute UPDATE or INSERT queries. Or more realistically, limit write access to those tables that are expected to changeâperhaps the session table and the user accounts table. By limiting what a user can do, you can detect, track, and defang many SQL injection at- tacks. Limiting access at the database level is supplemental: you should use it in addition to all of the database security mechanisms listed in this chapter.â (Reference: (http://dev.mysql.com/tech-resources/articles/guide-to-php-security-ch3.pdf))
Obviously the public needs to connect to the database to read and display data. However, for the website I am working on now all of my SQL does not require write-access privileges; with the exception of the âAdmin Control Panelâ pages where my client can add/edit/delete products from the database. Why not create two mysql users:
1) An admin user with write access. Only the admin php files will connect using this admin user, and they will be protected by my sessioncheck(); and my login form.
2) A public user with read-only access. All pages available to the public will connect using this public read-only user. They should be able to access data, but not change data.
Wouldnât this make SQL injection attacks pretty much impossible? Of course, my login form for the admin section would have to be VERY well protected. Is this concept common practice?
The public never connects to your database or even knows a database exists. Your PHP code, running on the web server, is what connects to the database.
Reread that article pmw57 linked a few posts ago, as many of the examples used only SELECT queries â which only read data. SQL injection isnât just about modifying data but accessing data you donât intend someone to have.
Thereâs nothing wrong with creating those separate users, but it does not address the SQL injection problem, which is a vulnerability of how you produce queries in code, not database access permissions.
It is possible, with just the right mistakes made by the coder, for someone to use SQL injection techniques to log in to your administration area without knowing the administratorâs password. At that point, the vulnerability has allowed the attacker to become a full fledged administrator in your system, and the lockdown of the read-only user is irrelevant. So, you have to address the SQL injection hole either way.
Permission management will not directly protect against injection. Indirectly, permission management will stop an action that the user is not allowed to do. For example, the perpetrator will still be able to inject your SQL, but perhaps not drop the database if that permission is restricted. Yet, they would be able to inject a select statement to say return some vital user data. Which they could then use to log in as an admin and bye bye goes the world.
Right, so I guess it may be an added layer of protection⌠but everything Iâve learned about SQL injection prevention must be applied everywhere on the site still. Prepared statements, input validation, etc. Do you guys think itâs worth it to set up another mysql user then? Or should I just stick with a single mysql user?
As a contrast, do you think that a computer should have only administrator accounts? Or do you think that thereâs a place for standard user accounts and limited accounts?
True⌠but like it has been said above, it will prevent DROP, INSERT, DELETE based attacks which directly manipulate database, but it wonât prevent SELECT based attacks which only require the read-only access. It wonât stop someone from finding a way to retrieve vital data, or bypass a login form; which is just as dangerous because then the attacker could just use my admin section pages to manipulate data. I guess it cannot hurt though. Do you guys utilize read-only users in practice?
