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

281–290 of 463 posts

Re: I Went to SQL Injection Court

#281

Post by brk »

zachrip wrote: post #225
tczMUFlmoNk wrote: post #217

Earlier quoted context omitted.

You can always `SELECT table_name, column_name, data_type FROM information_schema.columns`, which is part of the SQL standard. https://www.postgresql.org/docs/current/infoschema-columns.h... Plus, generally if you have SQL injection, you have multiple tries. You're not going to be locked out after one shot. And there's only so many combinations of `SELECT {id,userid,user_id,uid} FROM {user,users,login,logins,customer…

That's a good point, has anyone hardened a database by locking out users who select columns that don't exist? Or run other dubious queries? This would obviously interrupt production but if someone is running queries on your db it's probably worth it?

In the very early 2000’s I worked at a company building something along those lines. We could analyze SQL and SMB traffic on the fly and spot anomalous access to tables/columns/files, etc. Dynamic firewalling would have been the next progression if the company didn’t have other issues.

Re: I Went to SQL Injection Court

#282

Post by qingcharles »

tptacek wrote: post #267
qingcharles wrote: post #255

Earlier quoted context omitted.

Matt, you do the Lord's work. Bear in mind that Matt technically lost this, even with the backing of some of the absolute best civil rights lawyers in the country, Loevy and Loevy, fighting on his behalf. This shows you the absurd difficulty in fighting city hall, especially if you're crazy enough to do it without representation. The one thing working in our favor is what is proposed in TFA: change the law. Once the…

A losing public body is also generally on the hook for attorney's fees, which can be considerable. But the general problem here is that the public bodies are all spending someone else's money, so the real deterrent you have is how much of their time you can credibly threaten to eat up with legal actions.

That's true, as long as you are represented. I knew one lawyer in Illinois who would sit in FOIA court and take all the non-represented persons aside and offer to take their cases and split the attorney fees 50/50. I believe it isn't strictly above-board, but it is a solution to a problem.

People don't like being put under oath, so you can somewhat temper a public body's future refusals by deposing them or sticking as many of them on the stand. Especially with depositions, if you aren't represented then you can't be giving any attorney discipline for asking completely outrageous questions to force the deponent to admit crimes etc under oath.

Re: I Went to SQL Injection Court

#283

Post by tptacek »

aqueueaqueue wrote: post #279
tptacek wrote: post #98

Earlier quoted context omitted.

Because once you have the schema you can issue FOIA requests that include queries for them to run.

Oh wow! If that is necessary, that is so kafkaesque! "I want your data" "What data?" "What do you have?" "Ha ha. No. Tell me what you want" "Your data that is the metadata of your data" "Well actually..." ...

You can't ask public bodies to do research for you. That's the public policy balance in our FOIA laws: you can get almost anything (and: talk to Matt, you really can get a lot of stuff), but you have to be specific about what you're asking for, and it has to be "at hand" for the staff responding to the request.

Re: I Went to SQL Injection Court

#284

Post by dragonwriter »

qingcharles wrote: post #277
lcnPylGDnU4H9OF wrote: post #54

Earlier quoted context omitted.

Yeah, it's obvious the double standard here, then. Curious indeed why they are so adamant to keep the schema/data secret.

Public bodies tend to just want to resist FOIAs for the sake of resisting them. I've never really been able to fully understand the motivations, even after a decade of FOIA litigation.

I think it is likely to ne about budgets. That is, sure, FOIA and similar state laws usually allow the agency to collect something related to actual costs, but that's mostly meaningless since even if actually covers staff time it doesn't retroactively give them staff to cover it in the impacts areas, and often the FOIA volume doesn't effectively feedback into legislative budget processes for future staffing either, while their litigation needs are more likely to feed back into the legal staffing levels, so approving FOIA requests drains working resources in the area covering them in a way that fighting them does not in the immediate term, while fighting them also has the longer term benefit (from an agency perspective) of discouraging future requests.

Re: I Went to SQL Injection Court

#285

Post by [deleted] »

lIl-IIIl wrote: post #215
ic4l wrote: post #210

Earlier quoted context omitted.

I agree with you. Knowing the exact column names can speed up an attack and, in some cases, make it more feasible. Why don’t they just request disclosure of what’s actually stored and allow renaming of the columns? It seems odd that knowing the exact column names would be necessary if the goal is simply to understand what data is being stored and its intended purpose.

I wonder if that would be considered a "new report", which they don't have to provide.

[deleted]

Re: I Went to SQL Injection Court

#286

Post by wglb »

zachrip wrote: post #225
tczMUFlmoNk wrote: post #217

Earlier quoted context omitted.

You can always `SELECT table_name, column_name, data_type FROM information_schema.columns`, which is part of the SQL standard. https://www.postgresql.org/docs/current/infoschema-columns.h... Plus, generally if you have SQL injection, you have multiple tries. You're not going to be locked out after one shot. And there's only so many combinations of `SELECT {id,userid,user_id,uid} FROM {user,users,login,logins,customer…

That's a good point, has anyone hardened a database by locking out users who select columns that don't exist? Or run other dubious queries? This would obviously interrupt production but if someone is running queries on your db it's probably worth it?

I once did an security assessment for a product such as what you describe. Among other problems with it, the product itself had SQL injection vulnerabilities

For another example of what defenders are up against, see https://users.ece.cmu.edu/~adrian/731-sp04/readings/Ptacek-N.... This paper all but caused an upheaval in the WAF industry.

Re: I Went to SQL Injection Court

#287

Post by wglb »

dataflow wrote: post #223
IshKebab wrote: post #211

Earlier quoted context omitted.

'); SELECT * FROM logins --

This fails if either the UI sanitizes wildcards, or if the database prohibits them, or if it produces so much data that you can't ingest it in time, etc.

Sanitization almost always fails. This becomes an arms race.

Re: I Went to SQL Injection Court

#288

Post by tptacek »

qingcharles wrote: post #282
tptacek wrote: post #267

Earlier quoted context omitted.

A losing public body is also generally on the hook for attorney's fees, which can be considerable. But the general problem here is that the public bodies are all spending someone else's money, so the real deterrent you have is how much of their time you can credibly threaten to eat up with legal actions.

That's true, as long as you are represented. I knew one lawyer in Illinois who would sit in FOIA court and take all the non-represented persons aside and offer to take their cases and split the attorney fees 50/50. I believe it isn't strictly above-board, but it is a solution to a problem. People don't like being put under oath, so you can somewhat temper a public body's future refusals by deposing them or sticking a…

I went up against my muni over their refusal to release their police General Orders (which seems real dumb in retrospect; we got the General Orders from most of Chicagoland with no protest†). I reached out to Matt Topic, who offered to sue for free, or send a nastygram for a billable hour.

I ended up doing the latter, because I gotta work in this town, but one consequence of fee recovery is that it's much easier to get representation for a FOIA suit.

† https://github.com/jjarmoc/chicago-area-general-orders/

Re: I Went to SQL Injection Court

#289

Post by hunter2_ »

count wrote: post #274
zachrip wrote: post #225

Earlier quoted context omitted.

That's a good point, has anyone hardened a database by locking out users who select columns that don't exist? Or run other dubious queries? This would obviously interrupt production but if someone is running queries on your db it's probably worth it?

Zane Lackey (with Dan Kaminsky) gave a talk that discussed doing literally that sort of things, back in 2013. Zane went on to found Signal Sciences (acquired by Fastly), doing this sort of stuff in the 'WAF' space. https://youtu.be/jQblKuMuS0Y?t=866 (timestamp is when Zane starts talking about it)

I guess the main difference is that a WAF attempts to spot things like injection (unbalanced delimiters, SQL keywords in HTTP payloads where SQL shouldn't exist, etc.) typically without knowledge of the schema, whereas GP is talking about the DBMS spotting queries where queries must exist but disagree with the schema. Might as well do both, I suppose.

Re: I Went to SQL Injection Court

#290

Post by hunter2_ »

tptacek wrote: post #248
dmurray wrote: post #234

Earlier quoted context omitted.

And this part seems self-defeating: > Attackers like me use SQL injection attacks to recover SQL schemas. The schema is the product of an attack, not one of its predicates”. If it's the product of an attack, but not the end goal, surely it's of value to the attacker? It seems clear to me that the statute does, as worded, in principle allow the city not to disclose the database schema - it would compromise the securit…

Lots of things are "of value". That's not the bar the statute sets. To the extent something isn't per se exempted by the statute (as the outcome of the case established schemas are), the burden is on the public body to demonstrate that disclosure Would jeopardize the security of the system.

It still seems like a massively gray area: despite the distinction between "would jeopardize" and "could jeopardize" as explained by TFA, the definition of "jeopardize" includes "danger" which means "could lead to harm" not "would lead to harm" at which point it hardly matters whether a thing "could endanger" or "would endanger" the security of the system.
Post reply on HN