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

Show HN: sql_firewall – SQL Firewall Extension for PostgreSQL

github.com

Post reply on HN
Print view View original

11–16 of 16 posts

Re: Show HN: sql_firewall – SQL Firewall Extension for PostgreSQL

#11

Post by snaga »

sbuttgereit wrote: post #10
verelo wrote: post #2

This would be interesting from a query analytics perspective, but in terms of protection, i would personally not want to rely on something at the DB level. I would place a significant bet that this opinion is not unique to me, and it is why you do not find this built into xxSQL already. If the 'attacker' has access to execute unsafe queries, that is a problem you should address much further up the ladder rather than…

I see your comment is raising some controversy, but I tend to be in your camp on this one. I do a significant amount of development involving PostgreSQL and I don't think I would want to rely on this. I don't want to belittle the work in question, but as someone that can develop for the database, I have other, better techniques which can provide defensive in depth such as injection prevention while not relying on yet…

> there are many development shops that don't have strong database development talent on hand and really treat the database as some black box where to stuff data.

Exactly. That's the reason why we still see lots of SQL injection attacks and incidents, and the reason why I have created this module. :)

Re: Show HN: sql_firewall – SQL Firewall Extension for PostgreSQL

#12

Post by snaga »

michaelmior wrote: post #6

I'm assuming this works for prepared queries? That is, I should be able to execute a query which is the same, aside from different values for the bound parameters. Anyway, looks pretty cool :)

Yes, it works well with prepared queries. :)

Re: Show HN: sql_firewall – SQL Firewall Extension for PostgreSQL

#13

Post by snaga »

morgo wrote: post #4
ck2 wrote: post #3

Very clever. Hope to see a mysql port someday. There's a big argument going on in php-dev right now if php should do some basic, obvious injection protection. Of course that is controversial.

This already exists in MySQL (enterprise) here: https://dev.mysql.com/doc/refman/5.6/en/firewall.html

Yes, I think every database product needs similar solution to be protected. :)

Re: Show HN: sql_firewall – SQL Firewall Extension for PostgreSQL

#14

Post by thequux »

Neat! I did something similar as a patch to PostgreSQL proper in May of this year and gave a talk on it at BerlinSides[1]. I like the fact that your solution is implemented as an extension rather than as a patch to the core, but I worry about the fact that the extension API only has access to the post-analysis parse trees. While I'm comfortable saying that all of the types of SQLi attack that I'm aware of will cause a difference in the raw parse tree, I'm not so sure about whether those differences will carry through into the results of the analysis.

Also, how much overhead does this add? My software is linear (albeit with a fairly large constant) in the size of the input query and constant in the number of queries in the training set.

[1] https://github.com/thequux/postgres

Re: Show HN: sql_firewall – SQL Firewall Extension for PostgreSQL

#15

Post by anarazel »

thequux wrote: post #14

Neat! I did something similar as a patch to PostgreSQL proper in May of this year and gave a talk on it at BerlinSides[1]. I like the fact that your solution is implemented as an extension rather than as a patch to the core, but I worry about the fact that the extension API only has access to the post-analysis parse trees. While I'm comfortable saying that all of the types of SQLi attack that I'm aware of will cause…

> but I worry about the fact that the extension API only has access to the post-analysis parse trees.

I doubt that's a problem. Parse analysis won't remove information from the query - otherwise it'll not be available for the actual planning and execution ;)

Re: Show HN: sql_firewall – SQL Firewall Extension for PostgreSQL

#16

Post by snaga »

anarazel wrote: post #15
thequux wrote: post #14

Neat! I did something similar as a patch to PostgreSQL proper in May of this year and gave a talk on it at BerlinSides[1]. I like the fact that your solution is implemented as an extension rather than as a patch to the core, but I worry about the fact that the extension API only has access to the post-analysis parse trees. While I'm comfortable saying that all of the types of SQLi attack that I'm aware of will cause…

> but I worry about the fact that the extension API only has access to the post-analysis parse trees. I doubt that's a problem. Parse analysis won't remove information from the query - otherwise it'll not be available for the actual planning and execution ;)

> Parse analysis won't remove information from the query

That's right.

PostgreSQL's parse analysis keeps a statement structure with token-by-token in the parse tree, and PostgreSQL's query jumbling calculates a hash value from the parse tree.

So, it's possible to find something strange in the statement(s) if someone attempts to cheat.

Post reply on HN