Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

I Went to SQL Injection Court

sockpuppet.org

Post reply on HN
Print view View original

181–190 of 463 posts

Re: I Went to SQL Injection Court

#181

Post by zonkerdonker »

ajkjk wrote: post #149

This was fine, legally, but I'd be pretty irritated if someone I knew wasted everyone's time on this. The schema clearly is (marginally) useful for hacking, but who cares; it clearly is a file layout also, but who cares; those matter legally but not morally. Morally, this is just dumb: it's not something they really needed, and they're just irritating people and wasting resources for the fun of it. Shameful.

See here: https://news.ycombinator.com/item?id=43176625

FOIA requester responded in comments saying they received a tip indicating illegal practices, and noted in his article that he had previously uncovered evidence of over-policing in black neighborhoods.

Re: I Went to SQL Injection Court

#182

Post by foota »

mbreese wrote: post #168
foota wrote: post #144

Earlier quoted context omitted.

Out of curiosity, could you ask for something like "one row of data from every table in the CANVAS database"?

This is a technical solution to a people problem. My reading is that the city doesn’t want to give up this information. If that’s the case, a technical solution wouldn’t work, no matter how easy it is. And given that this has already gone to the Illinois Supreme Court (and lost), the only solution is what is discussed at the end: updating the law.

I agree this is something of a technical solution, but the court wasn't interpreting whether you could ask for rows from a database, but whether you could ask for the schema directly. I don't think the court had the option of saying "you can't ask for the schema, but asking for a sample row is ok".

Re: I Went to SQL Injection Court

#183

Post by jstanley »

default-kramer wrote: post #167
tptacek wrote: post #129

Earlier quoted context omitted.

To probe for what? How does knowledge of a column name make it easier for me to discern whether a SQL injection vulnerability exists? I've spent a lot of time in my career probing for SQL injection, and I can't remember an instance where my stimulus/response setup involved the table names. SQL injection is a property of a SQL query , not of the schema itself. To have a meaningful chance of blind-one-shotting a query,…

> How does knowledge of a column name make it easier for me to discern whether a SQL injection vulnerability exists? It doesn't. It just means that as soon as you find one, you can immediately begin crafting valid queries instead of randomly guessing table names and columns, therefore not setting off the "DB query failed" alert. EDIT: I guess this is the part I missed: > To have a meaningful chance of blind-one-shott…

You can craft valid queries that don't reference any table or column name.

Re: I Went to SQL Injection Court

#184

Post by chaps »

foota wrote: post #182
mbreese wrote: post #168

Earlier quoted context omitted.

This is a technical solution to a people problem. My reading is that the city doesn’t want to give up this information. If that’s the case, a technical solution wouldn’t work, no matter how easy it is. And given that this has already gone to the Illinois Supreme Court (and lost), the only solution is what is discussed at the end: updating the law.

I agree this is something of a technical solution, but the court wasn't interpreting whether you could ask for rows from a database, but whether you could ask for the schema directly. I don't think the court had the option of saying "you can't ask for the schema, but asking for a sample row is ok".

The short answer is yes, you can do this. I've seen this work for emails, where the request is basically, "Give me the most recent email of blah@gov.com".

And yeah, the plan was to eventually submit a batch of requests using the table names, similar to `SELECT * FROM {table_name_from_schema_request} LIMIT 1`, but one FOIA request per-table.

Re: I Went to SQL Injection Court

#185

Post by atkulp »

tptacek wrote: post #32
kasey_junk wrote: post #29

Earlier quoted context omitted.

The article describes why. 2 different db engines (or even instances) can use different file layouts for the same schema. In many was sql is all about divorcing the schema from the files.

Another way to think about it is that if a SQL schema is a file, so is an Excel spreadsheet template.

It's interesting that the opening analogy in the post uses an Excel spreadsheet as a great way to explain a database. It's such an easy next step to say the way an xls/ods file is saved is a file format but the column layout in the tabs/tables are the schemas. The court (and the city) playing these games is so scary since it is so biased toward all modern government data being covered by FOIA exemptions.

Re: I Went to SQL Injection Court

#186

Post by 9dev »

chaps wrote: post #56
doctorpangloss wrote: post #50

Earlier quoted context omitted.

What are the administrators of CANVAS hiding?

Hard to say. One of my personal drivers for this lawsuit is a tip I received that said that Chicago has a list of vendors whose tickets are dropped in the back-end. When I requested that info, the city said they had no such list. I trust my source, so having schema information could help figure out the extent and if they were lying.

Earnest question: If you suspect them of lying on the issue, why would you trust them to release the full schema in response to the FOIA request, and not just omit any possibly incriminating columns?

Re: I Went to SQL Injection Court

#188

Post by btucker »

tptacek wrote: post #55
pchristensen wrote: post #43

Earlier quoted context omitted.

My real goal is zoning. God speed to you sir! What is your goal wrt zoning?

The categorical elimination of single-family zoning along with any building envelope restrictions that would make as-of-right 3-flats uneconomical.

A step in the right direction last week for the largest upzoning effort in the city! https://archive.is/QuOcJ

Of course the a vocal minority is fuming about higher density.

Re: I Went to SQL Injection Court

#189

Post by ethbr1 »

lucb1e wrote: post #180
tptacek wrote: post #89

Earlier quoted context omitted.

If you specifically log failed database queries, where "failure" means "indicative of SQL injection", then nothing you can do with the schema is going to reduce the signal in that feed --- even a single SQL syntax error would be worth following up on. No, I don't think your logic holds.

> nothing you can do with the schema is going to reduce the signal in that feed --- even a single SQL syntax error would be worth following up on Syntax errors coming from your web application mean there is a page somewhere with a bugged feature, or perhaps the whole page is broken. Of course that's worth following up on? Edit: maybe I should add a concrete example. I semi-regularly look at the apache error logs for…

> Syntax errors coming from your web application mean there is a page somewhere with a bugged feature, or perhaps the whole page is broken. Of course that's worth following up on?

This is a government system, with apps probably built by lowest-bid contractors.

I imagine most of us would be horrified by the volume of everyday failed queries from deployed apps.

Re: I Went to SQL Injection Court

#190

Post by cyanydeez »

chaps wrote: post #184
foota wrote: post #182

Earlier quoted context omitted.

I agree this is something of a technical solution, but the court wasn't interpreting whether you could ask for rows from a database, but whether you could ask for the schema directly. I don't think the court had the option of saying "you can't ask for the schema, but asking for a sample row is ok".

The short answer is yes, you can do this. I've seen this work for emails, where the request is basically, "Give me the most recent email of blah@gov.com". And yeah, the plan was to eventually submit a batch of requests using the table names, similar to `SELECT * FROM {table_name_from_schema_request} LIMIT 1`, but one FOIA request per-table.

Seems like you could asked for a verbally masked description? Like an enigma coda specific to the FOIA.

"Describe to me the columns, in simple non-programmatic english, and what the purpose of the table is for, for each table related to parking tickets"

Essentially a human to schema DSL That is only technically decipherable by the admin of the database. Then you're not having actual code and only the admin could decipher.

But yah, as you said, if the humans don't want to disclose their foibles, how the request is filled is technically meaningless.

Post reply on HN