
How not to protect against SQL injection (view source) - ssclafani
http://www.cadw.wales.gov.uk/
======
somedev
It was me that actually built this site. Around 2000-2001. To give you a bit
of background or "excuses":

It was my first website at an agency, I'd just taught myself ASP and SQL in
just a few months previous (with no help or guidance). If my memory serves me
correct, that dodgy JavaScript was put in there by a more senior developer. I
had no idea what SQL Injection was and it wasn't until at least a few years
later that SQL Injection was even something any developers I knew were aware
of - The Wikipedia page for SQL Injection
(<http://en.wikipedia.org/wiki/SQL_injection>) under "Known real-world
examples" has the earliest dated at 2005 (but obviously, this vulnerability
has been around forever).

And yes, I'm still a Web Developer (front-end nowadays - that also knows much
better than this) and no, I no longer work for that agency and haven't for a
long time.

In response to some of the comments: * I've seen many many developers write
SQL Injection prone code at least 6 years after this was written. * Any
developer that was around during 2000-2001 would know that this was before the
time of CMS's (free or otherwise), libraries, frameworks, SQL abstraction
layers etc. * I'm pretty sure there is some server-side sanitising done too
(before we'd heard of the term SQL Injection). * I don't think it was using an
SQL login with drop permissions.

------
mixmax
I just fired off an e-mail to point out that they have a potentially serious
security problem and they should get it fixed ASAP.

I see this as a civic duty, and think that this is the kind of action you're
required to perform if you see a serious problem. Writing an e-mail takes ten
seconds, but the potential damage could well cost serious money.

~~~
derleth
If you're lucky, you aren't in the UK so they won't be able to arrest you
instantly on the hacking charges.

If you're very lucky, the place you are in won't honor their demands for
extradition on the hacking charges.

~~~
mixmax
No offense, but I think you're a tad paranoid. If I was a mechanic and I saw
someone at a gasstation driving a car that was obviously dangerous because of
some kind of bad fixup I would tell him. This is no different, and I don't
expect anyone to sue me for that.

Here's the mail I sent:

Hi there,

It appears that you have some pretty severe security problems on your site.
This is a heads up so you can get it fixed. I would recommend doing so ASAP.

Your site has been posted to hacker news (which is a friendly programming site
for start-up people and nerds) as an example of bad security practices. The
link is here: <http://news.ycombinator.com/item?id=2383857>

It has also been posted to Reddit, which might be more of a problem since that
site has a lot of 14 year old bored teens hanging around that know just enough
about programming to do a lot of damage... Link:
[http://www.reddit.com/r/programming/comments/gdviz/how_not_t...](http://www.reddit.com/r/programming/comments/gdviz/how_not_to_guard_against_sql_injections_view/)

It appears that your site is easy to compromise, which might lead to anything
from defacement to someone stealing all your content, usernames, passwords,
etc.

I have nothing to do with these postings, I just don't like to see innocent
sites get in trouble, hence this mail. Feel free to contact me if you need
anything or have questions.

Hope you get it fixed before someone breaks it.

Yours,

Max

~~~
nowarninglabel
I wouldn't say they were paranoid. A few months back I showed a colleague what
looked like the openings of a very serious data leak in a major company's
site. He investigated further and then reported it up through the chain of
command and then over to the company. At no point did he do anything other
than what was done here, as in point out a publicly visible security flaw. He
was nearly fired after the company threatened to sue. The company only
relented when he agreed to keep quiet and his employers disciplined him. The
employers didn't back him up. All this for solely reporting a flaw, absolutely
zero use of said flaw.

I lost a lot of my faith in humanity that day.

~~~
flexd
I would go find a new employer if that was the case, what bunch of idiots.

------
Joeri
The saddest part is that tons of people will be reading this thinking that
they're way smarter than that guy, while in fact their sites are wide open to
exploitation as well. That last statement probably applies to me too.

Doing web security well is hard, too hard. Everyone gets caught with a
security bug sooner or later, even google. It's easy to laugh with silly
coding like this, but I blame the technology for allowing SQL injection in the
first place. SQL is simply a bad API to be using in a web app.

~~~
Skalman
I don't think SQL is a bad API - it's just that every language makes it so
difficult to use prepared statements! It shouldn't be harder than:

    
    
        sql_query('SELECT * FROM mytable WHERE name = ?', name)
    

_(I'm aware that this defeats the purpose of prepared statements to be
reusable - this is just an API that's better than the current methods)_

~~~
steve-howard
The side benefit of prepared statements, perhaps even more importantly if
security is not really your concern, is that you don't need a password page
that looks like this (this is really the password requirements page for my
school):

A password must:

    
    
        be 6-8 characters in length.
        contain a non-alphanumeric character such as ( ! ] & * , + =
    

A password cannot:

... include a dollar sign ( $ ), a single quote ( ‘ ), a double quote ( “ ), a
number sign ( # ), a less-than sign ( < ), a question mark ( ? ), a pipe ( |
), a back quote ( ` ), or a backslash ( \ ). ...

~~~
reemrevnivek
I _hate_ those signups. The worst part about them is that they're usually on
sites I'm required to sign up for, like a school, work, or corporate service.

If it was a startup web app I was signing up for, I'd send the developer a
polite email saying that I didn't feel comfortable putting my data in such a
system. Unfortunately, all I can usually do is gripe a little in private.

------
elboru
"Terms & Conditions: SECURITY We are committed to ensuring that your
information is secure. In order to prevent unauthorised access or disclosure
we have put in place suitable physical, electronic and managerial procedures
to safeguard and secure the information we collect on line. We use encryption
when collecting or transferring sensitive data such as credit card
information."

I don't know why but I just don't trust them...

------
ZeroComplete
I'm going to assume that they have a server-side validation script running and
the client side code is just to prevent/explain to mistaken users and if the
server-side script every activates they know that someone's being malicious.

~~~
julianc
Maybe the javascript is intentional, like a honeypot for hackers :)

~~~
pbhjpbhj
Yeah, maybe the 100+ validation errors in the markup are like a honeypot for
web designers too ... I've not seen 1x1 gifs for a few years now.

And leaking the MS SQL server errors and IIS errors are just adverts for MS (I
only did genuine searches, "hotel" got me to an error page).

I'm sure the silly long names are part of the ruse too.

There is much that could be done with this site. Perhaps I could drop them a
CV.

~~~
somedev
See my reply above. Yep, 1x1 gifs and table layouts were cutting edge back
then ;)

------
MatthewPhillips
Since they're using SQL Server (hint is that they are checking for "xp_"), you
can get a list of all of their databases with "SELECT name FROM
sys.databases", then loop through and drop them. Hope the web login doesn't
have drop permissions.

~~~
tptacek
Are they actually vulnerable? How do you know? People have gotten in serious
trouble in the UK for "innocuously" testing web apps for SQL problems. Know
that in both the UK and the US, you are taking a significant risk by prodding
websites like this.

~~~
MatthewPhillips
I have no idea if they are, hence "I hope their web login doesn't have drop
permissions".

~~~
tptacek
Since people on Reddit are apparently actually poking this thing, I just want
to get in the warning: DON'T DO THAT.

------
RossDM
When I was working in the financial sector, I came across an email thread
involving a certain software vendor who had been notified of a SQL injection
vulnerability. To fix it, they created an IF statement that did a string
comparison to check for the exact SQL attack that had been used.

------
Animus7
I have a feeling that this site won't be up much longer after making front
page of HN, and it will have nothing to do with server load.

~~~
goalieca
It was on reddit yesterday I believe which means tons of kiddies saw that.
People were guessing that it included sanity checking on both sides because it
wasnt down yet.

------
iuguy
H.M. Government has a specific set of standards that apply to websites based
on the impact of information assets contained on them (as well as other bits
and pieces that I don't need to go into). The weird thing is, this site is for
the Welsh Assembly which, as a devolved government has to meet the standards
but is seen in certain respects as a 'foreign government' within the civil
service (our H.M. Government sector). Make no mistake, there are some things
that this site will have to comply with, but the implied and genuinely air-
quoted 'measures' put forward would add nothing to any of this.

A moderately large amount of this information is available on the Internet,
start at [http://www.cabinetoffice.gov.uk/resource-library/security-
po...](http://www.cabinetoffice.gov.uk/resource-library/security-policy-
framework) if you want a look. A brief look through the sitemap suggests they
are holding or processing Personally Identifiable Information (PII) which puts
them under the Data Protection Act. Again, the presence of the javascript
doesn't imply actual SQL injection, but it definitely doesn't imply a measure
against it.

In this instance, the compliance requirements are fairly low. I guess the exam
question is, can they pass the bar, or do they limbo under it?

------
jofabian
Funny is that I tried to warn them about that problem and their Feedback form
doesn't work.

~~~
dayjah
_facepalm_ \- though the 1/4 Englishman in me snickers at the Welsh!

------
Stormbringer
Would have been better protected if their javascript was programmed in
Welsh... :D

------
arpy
Poor old Bobby Drop Tables will be out of luck again.

------
teichman
So for those of use who know nothing about websites: what is the correct way
to protect against SQL injection?

~~~
tajddin
Generally, on the server-side, you parameterize the query. Depending on the
server-side language, a normal SQL query that would read SELECT * FROM myTable
WHERE lastName = 'Smith' would be converted to something like SELECT * FROM
myTable WHERE lastName = @lastnameparam. Then in code, you'd supply the value
of @lastnameparam as 'Smith'.

It depends on the language, but this is what you'd do in .NET, for example. In
this case, the framework does the work for you by encoding the value of
lastnameparam (it makes sure that whatever is supplied to lastnameparam isn't
read as SQL).

------
stevemoy
My take on this is that the scriptwriter's goal was not to stop SQL injection
attacks but rather prevent regular users from inadvertently screwing with the
database.

Looking at it that way makes it a much more understandable (and all-too-
common, unfortunately) oversight.

~~~
MattJ100
Erm... if the server-side was already escaping properly then there would be no
way for users to mess with the database. Only if it is not escaping properly
is this code vaguely useful.

It's not like you can't store semi-colons in an SQL database :)

------
gary4gar
Javascript - It can be Disabled!

Every Web Dev needs to remember this and Yet people tend to forget

~~~
MatthewPhillips
They use javascript to submit the form, so that's not a vulnerability for
them.

~~~
Devilboy
You can submit their form without using their JS

------
d2
There are more websites than competent admins so this kind of thing is
inevitable. If you were a nice guy you would have reported it to the admin and
left it at that.

------
rosenjon
Yeah...pretty sad. But at the same time, if the site isn't down by now, there
is probably server side checking in place as well.

------
Lozzer
The linked page seems very safe. It has a very bad form checking function, but
no actual form...

------
JohnnyBrown
Well, it's still up after 8 hours, so apparently there was some server-side
checking as well

------
evo_9
Well at least form & function are equals.

------
peterbe
Hello!!! Has anybody hacked the site yet? Perhaps it would be nasty to delete
all database tables but at least some sort of update would be funny?

------
ascendant
<https://addons.mozilla.org/en-us/firefox/addon/groundspeed/>

------
ignifero
They are not scared of sql injection cause they have Styled Scrollbars!!

------
vain
<http://news.ycombinator.com/item?id=2370022> (CEO Friday: Why we don’t hire
.NET programmers)

Would an open source programmer do something like this?

~~~
jarek
_cough_ php _cough_

------
thomasfl
Upvote here if you too have discovered sql injection vulnerabilities in your
own web apps.

~~~
eru
"Upvote here" doesn't work on HN.

~~~
ChuckMcM
Depends on your definition of 'work' doesn't it?

~~~
eru
Just trying to help somebody get used to the community norms.

