Comment On Poor Mr. O'Hare

When developing an application that uses in-line SQL queries, it's essential to protect against "SQL Injection", i.e. the insertion of SQL code as a result of a string concatenation. For example, if productNum comes directly from user input, it might contain a malicious string, such as "'; DROP DATABASE --"

myQuery = "SELECT Prod_Name FROM Products WHERE Prod_Num='" + productNum "';";

The best way to do this is through "parameterized queries," which lets the database libraries take care of it. The next best way is to write your own escaping functions. And the next best way is to replace all instances of "O'Reily" with "O''Reily".

INJECTION IS NOT YOUR FRIEND!

What if my login name is Mister '; DROP DATABASE;

(okay, that exact form wouldn't work, but you get the idea)

Simple, ban the Irish and the Scots.

Stupid. Where there is one special case there are a million, so, unless you want to fix them all by hand, it's time to re-imagine your logic.

I had the reverse problem once...I was working on a system where the system automatically threw out all SSNs that started with  "800". I got egg on my face because I told the user that that was impossible (I hadn't written the application, and I "knew" the guy who had hadn't been a MORON), and when she
showed me that it was possible, I set forth on an quest to find the one line of code that declared all 800 ssns invalid.

Sigh.

Am I to understand that each O'+(A-Za-z) surname needs to have its own set of 23 occurrences of that line of code?

NOOOOoooooooo!!!!!

Alex Papadimoulis:

When developing an application that uses in-line SQL queries, it's essential to protect against "SQL Injection", i.e. the insertion of SQL code as a result of a string concatenation. For example, if productNum comes directly from user input, it might contain a malicious string, such as "'; DROP DATABASE --"

myQuery = "SELECT Prod_Name FROM Products WHERE Prod_Num='" + productNum "';";

The best way to do this is through "parameterized queries," which lets the database libraries take care of it. The next best way is to write your own escaping functions. And the next best way is to replace all instances of "O'Reily" with "O''Reily".

Actually the best way of doing it is to use a real web programming language like ColdFusion, which will do all the escaping for you.  The best, best way is to do it in coldfusion with parametarized queries, but even if the coder is not smart enough to use them, this kind of issue just wouldn't even come up.    

These latest WTFs have been.... what's the word..... expected. Things like this happen all too often. I kind of wonder why MORE websites aren't hacked, as things like this are just so common.

I'm hoping the recent lack of code WTFs aren't from a previous comment I made... It was in jest... well, sorta ;-P



The TRUE wtf is that they didn't put all of the exceptions into a function named IrishLastNameConverter lol ;-P

Anonymous:

INJECTION IS NOT YOUR FRIEND!

Only when going through substance abuse rehabilitation!

Anonymous:

Actually the best way of doing it is to use a real web programming language like ColdFusion, which will do all the escaping for you.  The best, best way is to do it in coldfusion with parametarized queries, but even if the coder is not smart enough to use them, this kind of issue just wouldn't even come up.    

ColdFusion, a Real web programming language? Hahahahahahahaahahahahahahahahahahahahaha!!!!

Thanks for that! :)
    dZ.

DZ-Jay:

Anonymous:

Actually the best way of doing it is to use a real web programming language like ColdFusion, which will do all the escaping for you.  The best, best way is to do it in coldfusion with parametarized queries, but even if the coder is not smart enough to use them, this kind of issue just wouldn't even come up.    

ColdFusion, a Real web programming language? Hahahahahahahaahahahahahahahahahahahahaha!!!!

Thanks for that! :)
    dZ.

About 10 times better then ASP or PHP.  Maybe ASP.NET or JSP can compete with it, but nothing can beat the RAD functionality of ColdFusion.

Satanicpuppy:

Stupid. Where there is one special case there are a million, so, unless you want to fix them all by hand, it's time to re-imagine your logic.

I had the reverse problem once...I was working on a system where the system automatically threw out all SSNs that started with  "800". I got egg on my face because I told the user that that was impossible (I hadn't written the application, and I "knew" the guy who had hadn't been a MORON), and when she
showed me that it was possible, I set forth on an quest to find the one line of code that declared all 800 ssns invalid.

Sigh.

"Currently, a valid SSN cannot have the first three digits (the area
number) above 772, the highest area number which the Social Security
Administration has allocated."

http://en.wikipedia.org/wiki/Social_Security_number_%28United_States%29#Valid_SSNs

Either wikipedia's dead wrong, or someone was feeding your system invalid SSNs

Apparently the developer had never heard of the popular line of IT books, the "O'Hare Library."

DZ-Jay:

Anonymous:

Actually the best way of doing it is to use a real web programming language like ColdFusion, which will do all the escaping for you.  The best, best way is to do it in coldfusion with parametarized queries, but even if the coder is not smart enough to use them, this kind of issue just wouldn't even come up.    

ColdFusion, a Real web programming language? Hahahahahahahaahahahahahahahahahahahahaha!!!!

Thanks for that! :)
    dZ.

I used ColdFusion a little bit at my last job.  It made me want to CFPuke.  Zing!

Anonymous:

DZ-Jay:

Anonymous:

Actually the best way of doing it is to use a real web programming language like ColdFusion, which will do all the escaping for you.  The best, best way is to do it in coldfusion with parametarized queries, but even if the coder is not smart enough to use them, this kind of issue just wouldn't even come up.    

ColdFusion, a Real web programming language? Hahahahahahahaahahahahahahahahahahahahaha!!!!

Thanks for that! :)
    dZ.

About 10 times better then ASP or PHP.  Maybe ASP.NET or JSP can compete with it, but nothing can beat the RAD functionality of ColdFusion.

There are certainly other frameworks for developing web applications apart from ASP or PHP, and some of them focus on RAD, some of them even better, faster, and more robust and secure than your precious ColdFusion.  But I most definitely wouldn't call ColdFusion a "Real Programming Language".

    -dZ.

Anonymous:

Actually the best way of doing it is to use
a real web programming language like ColdFusion, which will do all the
escaping for you.  The best, best way is to do it in coldfusion
with parametarized queries, but even if the coder is not smart enough
to use them, this kind of issue just wouldn't even come up. 
  

Though, chances are if you do have a coder that is not smart enough to
conceptualize that building any dynamic data into any instruction set
(SQL, JS, etc) will require escaping to seperate dynamic data from
characters used by the language.... its better to know than suppress
the error condition (yes, there is no error in the code, but there
would be one in management).



JS:

alert("Wouldn't he "miss" this too?");



...and I bet the text field in a webform would probably read "O"
instead of "O'Hare" and in the source you'd see <input type='text'
name='user' value='O'Hare'>

Anonymous:

Anonymous:

Actually the best way of doing it is to use
a real web programming language like ColdFusion, which will do all the
escaping for you.  The best, best way is to do it in coldfusion
with parametarized queries, but even if the coder is not smart enough
to use them, this kind of issue just wouldn't even come up. 
  

Though, chances are if you do have a coder that is not smart enough to
conceptualize that building any dynamic data into any instruction set
(SQL, JS, etc) will require escaping to seperate dynamic data from
characters used by the language.... its better to know than suppress
the error condition (yes, there is no error in the code, but there
would be one in management).



JS:

alert("Wouldn't he "miss" this too?");



...and I bet the text field in a webform would probably read "O"
instead of "O'Hare" and in the source you'd see <input type='text'
name='user' value='O'Hare'>

It doesn't get suppressed, coldfusion just escapes it automatically when you use it in a query.  As far as input's there are functions to escape that as well, but it's not done automatically. 

Anonymous:

Actually the best way of doing it is to use a real web programming language like ColdFusion, which will do all the escaping for you.

Ew.  PHP optionally does that, it's called "magic_quotes_gpc", and any application that relies on it is considered crap.  Run escaping functions in appropriate places, or use parameterized queries.  Don't rely on the language to escape every piece of input for you, and then run a stripslashes()-esque function on the data that shouldn't be escaped.  That's just disgusting.  Although I guess we should expect terrible advice like this from a coldfusion "developer".

DZ-Jay:

There are certainly other frameworks for developing web applications apart from ASP or PHP, and some of them focus on RAD, some of them even better, faster, and more robust and secure than your precious ColdFusion.  But I most definitely wouldn't call ColdFusion a "Real Programming Language".

    -dZ.

Name one. 

And I didn't say it's a real programming language.  I said it's a "Real Web Programming Language".  It has not much use outside the web world, but in the web world it's one of the most robust, RAD platforms out there.  Seriously.  I dare you.  Name one other programming language that even comes close to CF in terms of RAD. 

Alex Papadimoulis:

When developing an application that uses in-line SQL queries, it's essential to protect against "SQL Injection", i.e. the insertion of SQL code as a result of a string concatenation. For example, if productNum comes directly from user input, it might contain a malicious string, such as "'; DROP DATABASE --"

myQuery = "SELECT Prod_Name FROM Products WHERE Prod_Num='" + productNum "';";

The best way to do this is through "parameterized queries," which lets the database libraries take care of it. The next best way is to write your own escaping functions. And the next best way is to replace all instances of "O'Reily" with "O''Reily".

Close.  Add the use of stored procedures and I'm all for it.  Oh wait, that would mean if you are using an earlier version of MySQL you probably never heard of these.

No, don't let ColdFusion escape it, use parameters.  Without parameters you are creating a security hole large enough to drive a truck through.  Do it right.  Also parameters are supported by most major database vendors and is more performant.

merreborn:

Anonymous:

Actually the best way of doing it is to use a real web programming language like ColdFusion, which will do all the escaping for you.

Ew.  PHP optionally does that, it's called "magic_quotes_gpc", and any application that relies on it is considered crap.  Run escaping functions in appropriate places, or use parameterized queries.  Don't rely on the language to escape every piece of input for you, and then run a stripslashes()-esque function on the data that shouldn't be escaped.  That's just disgusting.  Although I guess we should expect terrible advice like this from a coldfusion "developer".

Magic quotes is totally different.  ColdFusion only does automatic escaping inside queries, not on all your data.  Therefore there are no problems with having to un-escape things.  So you've proved my point, CF is better then PHP.

merreborn:

Satanicpuppy:

Stupid. Where there is one special case there are a million, so, unless you want to fix them all by hand, it's time to re-imagine your logic.

I had the reverse problem once...I was working on a system where the system automatically threw out all SSNs that started with  "800". I got egg on my face because I told the user that that was impossible (I hadn't written the application, and I "knew" the guy who had hadn't been a MORON), and when she
showed me that it was possible, I set forth on an quest to find the one line of code that declared all 800 ssns invalid.

Sigh.

"Currently, a valid SSN cannot have the first three digits (the area
number) above 772, the highest area number which the Social Security
Administration has allocated."

http://en.wikipedia.org/wiki/Social_Security_number_%28United_States%29#Valid_SSNs

Either wikipedia's dead wrong, or someone was feeding your system invalid SSNs

They're using the 800 series for Illegal Immigrants.

richleick:

Alex Papadimoulis:

When developing an application that uses in-line SQL queries, it's essential to protect against "SQL Injection", i.e. the insertion of SQL code as a result of a string concatenation. For example, if productNum comes directly from user input, it might contain a malicious string, such as "'; DROP DATABASE --"

myQuery = "SELECT Prod_Name FROM Products WHERE Prod_Num='" + productNum "';";

The best way to do this is through "parameterized queries," which lets the database libraries take care of it. The next best way is to write your own escaping functions. And the next best way is to replace all instances of "O'Reily" with "O''Reily".

Close.  Add the use of stored procedures and I'm all for it.  Oh wait, that would mean if you are using an earlier version of MySQL you probably never heard of these.

I have sort of a problem with stored procedures.  You can't keep them in subversion.  At least it's not easy.  With coldfusion, you can create parametized queries, which come very close to stored procedures, and can be stored in an svn repository. 

Anonymous:

DZ-Jay:

There are certainly other frameworks for developing web applications apart from ASP or PHP, and some of them focus on RAD, some of them even better, faster, and more robust and secure than your precious ColdFusion.  But I most definitely wouldn't call ColdFusion a "Real Programming Language".

    -dZ.

Name one. 
 

Ruby.

can you guys PLEASE find something more interesting to talk about than development platform wars.. it's so tired!

Anonymous:

Ah, another useless CF coder.

 

CF does *not* automatically prevent of SQL injection.

 

Doing this in CF is just as bad as the example quoted:

 

<cfquery>

select *

from table

where id = #id#

</cfquery>

 

The way to prevent SQL injection in CF is to use the <cfqueryparam> tag.

 

<cfquery>

select *

from table

where id = <cfqueryparam value="#id#">

</cfquery>

 

Gawd, why the hell are all CF users so damned smug and yet so dumb?

I never said that it automatically prevents SQL injection, but it does automatically escape single quotes. 

Although your example is one of the right ways to prevent sql injection, there are many more. 

<cfargument name="id" type="numeric">
are 2 other ways, and neither of them prevents you from caching the query. 

So stop shooting off your mouth when you don't know any better.   

Anonymous:

Anonymous:

DZ-Jay:

There are certainly other frameworks for developing web applications apart from ASP or PHP, and some of them focus on RAD, some of them even better, faster, and more robust and secure than your precious ColdFusion.  But I most definitely wouldn't call ColdFusion a "Real Programming Language".

    -dZ.

Name one. 
 

Ruby.

Personally, I haven't looked into Ruby yet, and although I've heard good things, I have doubts on whether it has half the functionality offered by ColdFusion.  So even If I concede this point, there are not a lot of platforms out there that can outperform ColdFusion, especially since it's a J2EE platform now.  If I have a problem with the performance, I can always throw together a java class that does what I need, and call it nativelly from coldfusion. 

Anonymous:

can you guys PLEASE find something more interesting to talk about than development platform wars.. it's so tired!

No, YOU'RE A TOWEL!!!

I have sort of a problem with stored procedures.  You can't keep them in subversion.  At least it's not easy.  With coldfusion, you can create parametized queries, which come very close to stored procedures, and can be stored in an svn repository. 

This has to be the most specious reason I've ever heard for not using stored procedures.  Granted, it's clunky to properly version them in any source code repository, but the benefits of using them (beyond just avoiding injection attacks) far outweigh this minor inconvenience.

Anonymous:

Name one. 

And I didn't say it's a real programming language.  I said it's a "Real Web Programming Language".  It has not much use outside the web world, but in the web world it's one of the most robust, RAD platforms out there.  Seriously.  I dare you.  Name one other programming language that even comes close to CF in terms of RAD. 

I haven't used CF in a long time... is the following still true?

Cold Fusion is to (Ruby On Rails | PHP | JSP) what Visual Basic is to (C++ | C# | Java)

Anonymous:

I have sort of a problem with stored procedures.  You can't keep them in subversion.  At least it's not easy.  With coldfusion, you can create parametized queries, which come very close to stored procedures, and can be stored in an svn repository. 

This has to be the most specious reason I've ever heard for not using stored procedures.  Granted, it's clunky to properly version them in any source code repository, but the benefits of using them (beyond just avoiding injection attacks) far outweigh this minor inconvenience.

Such as?  I can write any stored procedure inside a CF query, and it will probably run in about the same time if I use parameters to call it.  Other then speed, what other benefits are there?

mhughes:

Anonymous:

Name one. 

And I didn't say it's a real programming language.  I said it's a "Real Web Programming Language".  It has not much use outside the web world, but in the web world it's one of the most robust, RAD platforms out there.  Seriously.  I dare you.  Name one other programming language that even comes close to CF in terms of RAD. 

I haven't used CF in a long time... is the following still true?

Cold Fusion is to (Ruby On Rails | PHP | JSP) what Visual Basic is to (C++ | C# | Java)

First of all, it's ColdFusion, the space from the name was dropped about 10 years ago.

CF has a plethora of frameworks available to it (such as Rails for Ruby), CF on Wheels intends to be a direct competitor to Ruby on Rails. 

PHP is not even in the same category as it's a piece of crap. 

JSP I believe is more efficient then coldfusion, but a lot harder to code in.  Basically you are trading execution speed for development time.  And with the speed of the servers on the market today, do you really want to do that?

ColdFusion runs on top of J2EE sort of like JSP.  It also allows you to call java classes nativelly.  So basically it's JSP that's a little slower, but lets you develop applications a lot faster.

   

Anonymous:

Anonymous:

I have sort of a problem with stored procedures.  You can't keep them in subversion.  At least it's not easy.  With coldfusion, you can create parametized queries, which come very close to stored procedures, and can be stored in an svn repository. 

This has to be the most specious reason I've ever heard for not using stored procedures.  Granted, it's clunky to properly version them in any source code repository, but the benefits of using them (beyond just avoiding injection attacks) far outweigh this minor inconvenience.

Such as?  I can write any stored procedure inside a CF query, and it will probably run in about the same time if I use parameters to call it.  Other then speed, what other benefits are there?

Seriously?  For one, stored procedures are more secure.  By assigning permissions you can determine what users/groups can execute them.  The get compiled and optimized by the database.  I honestly can't believe this is even a question.  I don't mean to sound snobby or arrogant, but come on.
.

Anonymous:

I have sort of a problem with stored procedures.  You can't keep them in subversion.  At least it's not easy.  With coldfusion, you can create parametized queries, which come very close to stored procedures, and can be stored in an svn repository. 

This has to be the most specious reason I've ever heard for not using stored procedures.  Granted, it's clunky to properly version them in any source code repository, but the benefits of using them (beyond just avoiding injection attacks) far outweigh this minor inconvenience.

I used to think it was not practical to version stored procedures when I worked for a bunch of cowboys.  After switching to a company that has change management (the only way to deploy code is through the source repository) it's not that much of a hassle, and it's nice to always know what version is in each environment.

Some management tools, SSMS for example, have integrated source control.  Even without such a tool the benefits far outweigh the minor inconvenience of checking out a script file.

Anonymous:

Actually the best way of doing it is to use a real web programming language like ColdFusion, which will do all the escaping for you.  The best, best way is to do it in coldfusion with parametarized queries, but even if the coder is not smart enough to use them, this kind of issue just wouldn't even come up.    

Troll.  Set IGNORE ON

Anonymous:

Anonymous:

Anonymous:

DZ-Jay:

There are certainly other frameworks for developing web applications apart from ASP or PHP, and some of them focus on RAD, some of them even better, faster, and more robust and secure than your precious ColdFusion.  But I most definitely wouldn't call ColdFusion a "Real Programming Language".

    -dZ.

Name one. 
 

Ruby.

Personally, I haven't looked into Ruby yet, and although I've heard good things, I have doubts on whether it has half the functionality offered by ColdFusion.  So even If I concede this point, there are not a lot of platforms out there that can outperform ColdFusion, especially since it's a J2EE platform now.  If I have a problem with the performance, I can always throw together a java class that does what I need, and call it nativelly from coldfusion. 

Do not question the Dave!

Anonymous:

Anonymous:

I have sort of a problem with stored procedures.  You can't keep them in subversion.  At least it's not easy.  With coldfusion, you can create parametized queries, which come very close to stored procedures, and can be stored in an svn repository. 

This has to be the most specious reason I've ever heard for not using stored procedures.  Granted, it's clunky to properly version them in any source code repository, but the benefits of using them (beyond just avoiding injection attacks) far outweigh this minor inconvenience.

Such as?  I can write any stored procedure inside a CF query, and it will probably run in about the same time if I use parameters to call it.  Other then speed, what other benefits are there?

Straight from the Cold Fusion people:
http://www.adobe.com/devnet/coldfusion/articles/beg_storedproc_02.html

MAN!

Wow, they were lucky that it wasn't Mr O'Drop Table who applied there.

Note: I know that this won't work in a query, but imagine it does.

Wikipedia's page on SSNs is incomplete.  It doesn't mention railroad SSNs, nowhere near the entire set of which were ever allocated (and the set has been retired so there will be no more of them.)  There are also holes, IIRC, because not all the prefixes have been assigned.

themagni:

merreborn:

Satanicpuppy:

Stupid. Where there is one special case there are a million, so, unless you want to fix them all by hand, it's time to re-imagine your logic.

I had the reverse problem once...I was working on a system where the system automatically threw out all SSNs that started with  "800". I got egg on my face because I told the user that that was impossible (I hadn't written the application, and I "knew" the guy who had hadn't been a MORON), and when she
showed me that it was possible, I set forth on an quest to find the one line of code that declared all 800 ssns invalid.

Sigh.

"Currently, a valid SSN cannot have the first three digits (the area
number) above 772, the highest area number which the Social Security
Administration has allocated."

http://en.wikipedia.org/wiki/Social_Security_number_%28United_States%29#Valid_SSNs

Either wikipedia's dead wrong, or someone was feeding your system invalid SSNs

They're using the 800 series for Illegal Immigrants.

The use of 800-series SSNs is not restricted to illegal immigrants.

When I was in the USAF I worked medical records, which are filed by SSN. On base would be foreign military members and family members, on exchange for training or layover, who had no SSNs. SSN is required for medical treatment in a military medical facility. For all such foreign nationals, they are assigned a SSN which starts with 800. the rest is determined by DOB. If two or more such people have the same DOB, the first three digits are incremented.

richleick:

Alex Papadimoulis:

When developing an application that uses in-line SQL queries, it's essential to protect against "SQL Injection", i.e. the insertion of SQL code as a result of a string concatenation. For example, if productNum comes directly from user input, it might contain a malicious string, such as "'; DROP DATABASE --"

myQuery = "SELECT Prod_Name FROM Products WHERE Prod_Num='" + productNum "';";

The best way to do this is through "parameterized queries," which lets the database libraries take care of it. The next best way is to write your own escaping functions. And the next best way is to replace all instances of "O'Reily" with "O''Reily".

Close.  Add the use of stored procedures and I'm all for it.  Oh wait, that would mean if you are using an earlier version of MySQL you probably never heard of these.

Uh ... I think parameterized queries = stored procedures.  Is there another kind I don't know about?

Anonymous:

I have sort of a problem with stored procedures.  You can't keep them in subversion.  At least it's not easy.  With coldfusion, you can create parametized queries, which come very close to stored procedures, and can be stored in an svn repository. 

And, as we all know, design decisions are best made in terms of what's most convenient for the developer.

Anonymous:

Simple, ban the Irish and the Scots.

And the Vulcans.

Anonymous:

DZ-Jay:

There are certainly other frameworks for developing web applications apart from ASP or PHP, and some of them focus on RAD, some of them even better, faster, and more robust and secure than your precious ColdFusion.  But I most definitely wouldn't call ColdFusion a "Real Programming Language".

    -dZ.

Name one. 

And I didn't say it's a real programming language.  I said it's a "Real Web Programming Language".  It has not much use outside the web world, but in the web world it's one of the most robust, RAD platforms out there.  Seriously.  I dare you.  Name one other programming language that even comes close to CF in terms of RAD. 

who ever told you that RAD is good for you? Ever heard about PAD? (*propper* application development)

so of course the fix was to put:

if (sLogin == "O'Reily") sLogin = "O''Reily";
if (sLogin == "Robert O'Hare") sLogin = "Robert O''Hare";

maybe we could create a funcion to look for "O'" and replace it with "O''"!!!

done!

If an organization has been using SSNs as their customer identifier, and enough customers have finally complained enough that they realize they have to stop, then they give out IDs in the invalid range.

My 'SSN' at a local college is a '999'.

Bus Raker:

richleick:

Alex Papadimoulis:

When developing an application that uses in-line SQL queries, it's essential to protect against "SQL Injection", i.e. the insertion of SQL code as a result of a string concatenation. For example, if productNum comes directly from user input, it might contain a malicious string, such as "'; DROP DATABASE --"

myQuery = "SELECT Prod_Name FROM Products WHERE Prod_Num='" + productNum "';";

The best way to do this is through "parameterized queries," which lets the database libraries take care of it. The next best way is to write your own escaping functions. And the next best way is to replace all instances of "O'Reily" with "O''Reily".

Close.  Add the use of stored procedures and I'm all for it.  Oh wait, that would mean if you are using an earlier version of MySQL you probably never heard of these.

Uh ... I think parameterized queries = stored procedures.  Is there another kind I don't know about?

uh?...

parameterized queries != stored procedures

Anonymous:

Actually the best way of doing it is to use a real web programming language like ColdFusion, which will do all the escaping for you. 

Can this be tommorow's WTF?  I mean, how many layers of application does one really need?  Oops .. guess I am including .net with that blanket statement.  Hmmm...

http://en.wikipedia.org/wiki/Coldfusion#Criticism