Tuesday, April 15, 2008

Insecure data

This is the most extraordinary security flaw. It was discovered in Oklahoma's online sex offenders register, something the state was compelled by the Federal government to offer:

Mousing over that "Print Friendly" link revealed this rather long URL:

http://docapp8.doc.state.ok.us/pls/portal30/url/page/sor_roster?sqlString=select distinct o.offender_id,doc_number,o.social_security_number,o.date_of_birth,o.first_name,o.middle_name,o.last_name,o.sir_name,sor_data.getCD(race) race,sor_data.getCD(sex) sex,l.address1 address,l.city,l.state stateid,l.zip,l.county,sor_data.getCD(l.state) state,l.country countryid,sor_data.getCD(l.country) country,decode(habitual,'Y','habitual','') habitual,decode(aggravated,'Y','aggravated','') aggravated,l.status,x.status,x.registration_date,x.end_registration_date,l.jurisdiction from registration_offender_xref x, sor_last_locn_v lastLocn, sor_offender o, sor_location l , (select distinct offender_id from sor_location where status = 'Verified' and upper(zip) = '73064' ) h where lastLocn.offender_id(%2B) = o.offender_id and l.location_id(%2B) = lastLocn.location_id and x.offender_id = o.offender_id and x.status not in ('Merged') and x.REG_TYPE_ID = 1 and nvl(x.admin_validated,to_date(1,'J')) >= nvl(x.entry_date,to_date(1,'J')) and x.status = 'Active' and x.status <> 'Deleted' and h.offender_id = o.offender_id order by o.last_name,o.first_name,o.middle_name&sr=yes
For the non-technical reader, the bit after "?sqlString=" in the url is the actual query used to pull data from the database and since it's right there in the url the query terms can be changed. This was used to pull every individual record including social security numbers from the system.

It's an unbelievably dumb way to structure an application.


Anonymous said...

It's a great deal worse: If it lets you execute arbitrary SQL, you can play other games, like deleting records, inserting new ones, or altering existing ones.

I'm not impressed by a sex offender registry that lets offenders remove themselves from it, and lets jokers add their neighbors or ex-girlfriends.

Peter Risdon said...

Very good point.

Anonymous said...

Line after line of arbitrary-looking crap: it reminds me of the old IBM JCL whose whole purpose seemed to be to obstruct the intelligent use of computers. People used to pass groups of punched cards to each other, saying "No-one knows why, but this lot works".

FlipC said...

Minor word of warning for anyone in the UK who thinks about fiddling with the SQL string to access extra data, you could face up to six months in jail under the Computer Misuse Act 1990; I really wish I was joking.