Your method, Village Idiot, for which you're arguing the point is fair in so far as it goes. However, it's not the 'be all and end all' of protecting against SQL injection -- or is it? Did I just give an opinion, or fact?

Rather than bickering about whether method x or method y will get the job done for situation z, why not discuss the shortfalls, advantages, disadvantages, workarounds for other types of situations for which method x and/or method y might not prove entirely satisfactory (or indeed, may prove to be ideal!) within the scope of discussion (SQL injection).

The main proponents of this topic appear knowledgeable on the subject, so rather than butting heads how about continuing and pushing the discussion along rather than battling it out between yourselves?

To get things started, here's one idea. The discussion thus far has really only covered one part (unless I'm being blind) of the spectrum of attacks: getting some SQL injected into the value part of a comparison (e.g. ... my_column = '<sql injection>' ...). How does one protect against non-column-value injection vectors? Sub-thoughts: Are they a concern to you? Or even covering, "what the hell are they?" might be a good start.

In that line of thought, here's one of my bookmarks on SQL injection, possible vulnerabilities, possible solutions, and the such. I would write more, but I'm ~(lazy|in\sclass|doing\sa\stest)~

[The Unexpected SQL Injection] Web Security Articles - Web Application Security Consortium

I am not against his method, he is all out calling mine insecure without backing up his statements.

And you can talk to any of my clients, they will all tell you I dont blow up at them, ever.

That is fact, my method is NOT a 'be all end all', nor did I ever remotely suggest it was. I said in many posts that mine is my style and his is his; they both work. It goes down to style, but he is calling mine insecure because he doesn't like the style.


I talked about the shortcomings of both and acknowledged that typecasting is in some instances completely nessessary.

I would love to, but I will not sit there and be called a newbie to security when I am not.

I am not sure what that term means, could you please clarify what that is? I even googled the term and got nothing, so I dont think that is the types common name.

Essentially both our methods do very similar things
My method: Escape every not integer and and put everything in single quotes. Therefore this is no way anything in the value that can be harmful. To avoid any possible mistakes, put all values being used in quotes.

Typecasting: Typecast all integers to int so there is no way that a harmful value can be assigned to it. If it is a string, escape it and put in quotes when in query (this part is identical to my method). Only put strings in quotes.

Really, neither of them are 'wrong' ways to do it, its is all coding preference.

__________________

In reference to delays link, neither of the methods we mentioned deal with that. You cant use wildcard on an int field. As for using it for text, I can not think of a way it could possibly be a security error if you validate your data. However, that is so specific to what you are doing, there is no single method to doing it.

__________________

I was merely getting across the idea that the only injection discussed so far relates to, generally, items in the WHERE clause specifically comparing the value of a column be it integer, string or other type. I've no idea what the proper term is, so column-value was a sufficient stand in.

So we've looked at things like SELECT ... WHERE blah='<injection risk>' or INSERT ... SET blah='<injection risk>' but there are other vulnerable (for want of a slightly more descriptive word) parts to queries that people might not think about. That was my aim for discussion. Really, I'm just trying to steer the conversation away from the stale position it was at.

Exactly. It was meant as a reference point for the discussion to follow different paths. There's a lot of material linked at the bottom of the page on different SQL vulnerabilities, and the page itself starts off by saying "what can happen, even when mysql_real_escape_string is used"...

An SQL injection can occur within any portion of any SQL Query.
Inserts, Deletes, Updates, and Selects any query that contains user-input is susceptible to SQL Injection.
Even undefined variables in some cases where Register Globals is enabled on the server.

There’s a couple of important aspects that one must consider when writing SQL interfaces in PHP, and when done properly in every query, will guarantee your safety against SQL Injection, which as we’ve seen, is the most common and easiest attack that can be performed on a given site.

First, when grabbing user input, always check to ensure the variable is set before proceeding...
Whereas using will throw a PHP Warning of undefined index if the variable is unset. -- This is just good practice and does not necessarily improve security unless display errors is turned on in your php.ini file.

If the variable is not set and you are using it in a where clause or inserting it into the database, you might want to consider bypassing the query if the variable is empty.
For example:
If we just run that query, we may be querying blank values. Now although the above is protected from SQL Injection by using mysql_real_escape_string in the string input variables, single quotes on string input, and intval type-casting in the user-input integers, we will have an SQL error, which in some cases, may display to the user.
Never do you want to display these errors to the user -- unless you are specifically debugging. Hackers typically look for invalid inputs, header 500 errors, SQL errors and others to determine where vulnerabilities lie.
Therefore it is good practice to ensure the the data is present before running the query
On a side note, single quotes is preferred to double quotes.
Single quotes do not process variables within the quotes and therefore have a 46% speed performance advantage over using double quotes.
The exception, of course, is when you want to use double quotes to process variables within the string as demonstrated above.

One other note I would like to point out is that using is_numeric() as a method of sanitisation is not a sufficient check in many cases. for example, will pass as a valid is_numeric variable.
However, this is not an SQL Injection concern, as it would be impossible to pass malicious code, but you will end up with an SQL Error if your database field is expecting an integer. (non WHERE clause)

In summary, use mysql_real_escape_string() on user input strings along with ensuring you use single quotes (back-ticks are not useful in these cases), and forcing integer or float/double on numeric inputs.
Always double check to ensure you are sanitising your variables properly and that even when invalid user-input is entered.

__________________
- Highway of Life
[ Software Engineer | PHP Developer | phpBB.com Team Member ]
phpBB Academy at StarTrekGuide

Village Idiot, your method isn't insecure. You are being lazy and treating numbers as strings. You aren't taking the time to typecast variables properly.

__________________
Eric

You mean I am not conforming to your coding style. So long as I verify incoming data as anyone would have to (even if you typecast) there is no error in it. Lazy would be not escaping it. There is no "proper" or "official" standard to this.

__________________

It is not my programming style. This "style", as you call it, has been around for as long as people have been programming. It is proper code. It is the standard for any programming language to treat variables as the proper type. Yes you can get away with it in PHP/MySQL but that doesn't mean it's correct.

If I looked at your code I would be concerned that you don't understand variable types.

My biggest problem is you are trying to teach people to be lazy programmers and not treat variables with the proper type in SQL queries.

__________________
Eric

Some languages force types, some dont. Just because C++ or Java force you to keep your types doesnt mean it is bad practice to not do so in PHP. As long as you verify your data like you would have to either way, it is a null point. I am simply taking what I feel is an not needed step out. I am placing a second method that works just as well on the table, people can do whatever they want. You may call it lazy, but it is certainly not. Which one you use is a matter of opinion, simple as that.

Really your could call any efficiency (for lack of a better word, it would be arrogant to call my method that) in programming lazy, but its only bad practice if it leads to messy code or has a downside.

__________________

Village Idiot,

I don’t think any of us really care how you create your own code or how you use it. -- Because we don’t use your code.
What we do care about is that you’re telling people that variable type safety is completely unnecessary, which is absolutely false.
I’m not saying your sanitisation are not going to work, or that there is some vulnerability in what you are doing, or how you are doing it. I do not know, I’ve not looked at the code, nor do I truthfully care.

The point is people need to code PHP correctly. PHP was not built to force variable types, like C or Java, it was built to be more dynamic type variable assignment -- type juggling.
It does not mean that you don’t need to pay attention to type safety, or that type casting is not important. It just means that you have more control over your variable type, but type safety is still extremely important.

People need to code PHP the way it was *meant* (by the developers of PHP) to be coded, that is using type juggling the way it was meant to be used. If you choose not to, that’s your choice.
But don’t push false PHP programming philosophies onto others who are trying to learn how to keep their sites and scripts secure, who may not be as familiar with SQL Injections or Security in General, or may not be that comfortable with PHP yet.
Not everyone is using the same PHP library for their backend and Database interaction. Therefore it is absolutely imperative that they understand how important type safety is in the code that they are working with. Just as it is important for them to understand how SQL Injection vulnerabilities work so that they can protect themselves against such attacks.

You will not find a single PHP Security Expert who will tell you that type casting is not important.
Consider this small example, a quote from Stefan Esser of SektionEins, debatably PHP's leading Security Expert, on a framework that he performed a code audit (Security audit) of not long ago:

__________________
- Highway of Life
[ Software Engineer | PHP Developer | phpBB.com Team Member ]
phpBB Academy at StarTrekGuide

Lol is that arguing I smell?

By the way, when hackz0ring sites, i usually do dis:
SHOW TABLES

this shows meh all da tables in da database, i own.

__________________
VillageIdiot can have my babbies ;d

So thats why you have no logical argument against my code. And honestly, I don't care what code you use, because I dot use it. And I do NOT say that type safety is completely unnecessary, I am saying there are alternates to it for this purpose. Please don't misquote me. Also, you don't seem to hold to that last part in the rest of your post, you do believe that my method is vulnerable to the point where you will tell people I am wrong.

And you are a definer of how it was meant to be done? Are you really that arrogant?

I was about to say the same to you.


Absolutely true

Here is the entire quote
He is by no means saying it is imperative. Please use your quotes in context.

I will congratulate you in that you are an excellent speaker, probably better than I have potential to be. Anyone who isn't closely scrutinizing your posts would most likely accept what you say as fact. However your arguments are almost all based off of opinion. All you have talked about so far is why typecasting works; which I agree with, typecasting works. What you haven't shown is how my method insecure in any way. You yourself admitted you didn't even read my code.

Unless you can show me how my method is insecure and your method would fix it, please don't try to slander me any more. Your method is NOT the the 'end all be all' method, please stop acting like it is (saying that a method you didnt even read is insecure and the maker is wrong is acting as such).

edit: Now I am quite sure you are just going to make a post about the glory of your method, without really backing up your claims regarding my method. Please don't waste your typing, I simply wont reply to any more posts based off of opinion, I have said everything worth saying unless you are going to start addressing backing your claims about my method with fact.

__________________

*bangs head off table*

Calm down childrens, gather around daddy. :P

I'm sorry that I'm completely off-topic, but you guys are really funny.

__________________
VillageIdiot can have my babbies ;d

Now don't make me turn this thread around! One.... Two....

__________________

Rolling on the floor laughing.

__________________
VillageIdiot can have my babbies ;d

Someone asked me today what the performance difference would be if they used mysql_real_escape_string() on all of their variable inputs into a database instead of casting to integers for the int inputs.
So I created the following benchmark for them and thought I would share it with you all. :)

It’s important to note that all user input strings must to go through mysql_real_escape_string() function for proper sanitisation.
But for integers, what about those? should we use the escape_string function on them, or force to var type int?
Lets find out what the performance differences are:

Result:
Connected successfully
benchmark: Begin
escape_string time: 0.697912931442
intval time: 0.349272966385
int time: 0.16778588295
intval vs escape_string difference: 0.348639965057
int vs escape_string difference: 0.530127048492
int vs intval difference: 0.181487083435
Percent faster intval is than escape: 50%
Percent faster int is than escape: 76%
Percent faster int is than intval: 52%

The result of the sanitisation:
escape_string: 12345\'?;/FOO/FUM\\\';Example String =
intval func: 12345
cast int: 12345

So from the benchmark, we can determine that (int) produces a 70% - 75% performance advantage over using mysql_real_escape_string();
In addition, if you are using an UPDATE or INSERT query, forcing var type int on integer variables will mean that your query will function properly, whereas an intended int variable that contains a string which goes through the escape_string function is going to throw an SQL Error if the SQL database is expecting a float or integer value of some sort.

Of course, don’t take my word for it... the above script is provided for you to test yourselves as well. :)
Enjoy!
- Highway of Life

__________________
- Highway of Life
[ Software Engineer | PHP Developer | phpBB.com Team Member ]
phpBB Academy at StarTrekGuide

Some conservatives have Coach Factory Outlet pushed that critique further, saying that Mr. Obama’s policies are too costly, often assist the wrong people Louis Vuitton Belts and could have the paradoxical effect of driving up college costs. The dispute turns not just on different Coach Factory Outlet assessments of how policies play out, but on differing philosophical views about the role of government. During Gucci Belts his time in office, Mr. Obama has sharply increased aid to low- and middle-income students, notably through the Pell Grant Coach Factory Outlet program, which grew from $14.6 billion given to 6 million students in 2008, to nearly $40 billion for Coach Factory Outlet almost 10 million students this year. His administration also made it easier to request aid, shortening the Coach Factory Online complex federal application and allowing people to transfer their financial information electronically from the Internal Coach Outlet Online Revenue Service database. But while many education experts laud his efforts, analysts of varying political Coach Outlet Online stripes have also questioned how much impact some of the president’s policies will have, noting that the prices Coach Online Outlet charged by colleges, and student borrowing, continue to climb.But behind the headlines about soaring costs, the Coach Factory Outlet Online reality is more complex and wildly uneven, because a growing number of students receive Coach Outlet Online financial aid, and only relatively high-income families pay those fast-rising sticker prices. Adjusted for Coach Factory Online inflation, the College Board calculates, the average net price changed little over the last decade at private Coach Factory Outlet schools, and rose only modestly at public ones.Defending federal spending, Arne Duncan, the secretary of Hermes Belts education, said that for more than 30 years, college prices had risen even when federal aid had not, leading him to believe Coach Factory Online there was zero correlation.