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

Simple API with Nginx and PostgreSQL

rny.io

Post reply on HN
Print view View original

41–50 of 74 posts

Re: Simple API with Nginx and PostgreSQL

#41

Post by ams6110 »

jeffdavis wrote: post #6
ricny046 wrote: post #4

Earlier quoted context omitted.

This example is not secured against SQL injections but you can do it easily. Look at the documentation here: https://github.com/FRiCKLE/ngx_postgres/

To clarify, it looks like "postgres_escape" is the way to do escaping. Unfortunately, it seems a bit awkward to differentiate between empty and NULL strings. That's something to be careful of. Also, I really think this should be included in the blog post, even if it's simple. Protecting against SQL injection is not optional, so leaving it out only muddies the comparison with more traditional frameworks. Also, there's…

From the docs re postgres_escape: "Because nginx cannot tell the difference between empty and non-existing strings, all empty strings are by default escaped to NULL value."

This behavior actually is what anyone who has used Oracle is accustomed to (empty string is NULL). I don't recall the default behavior in Postgres but Postgres is "Oracle-ish" in a lot of ways so I would be surprised if this is not the default there as well.

Re: Simple API with Nginx and PostgreSQL

#42

Post by jeffdavis »

sehrope wrote: post #34
rgbrenner wrote: post #30

Earlier quoted context omitted.

"protection from SQL injection attacks" In the module he's using, there is a postgres_escape function.. so there's no reason he couldn't have used it. http://labs.frickle.com/nginx_ngx_postgres/README

Unfortunately when defaults are inherently insecure, they lead to people building insecure systems. If every single parameterized SQL command needs to include (possibly multiple) escapes then it'll be missed in some places. It's unnecessary anyway. It would be way better would be if the parameters were bound as named parameters. Ex: location ~ /articles/(? \d+) { postgres_pass database; rds_json on; postgres_query HE…

"The DB driver should parse the parameter and bind it as a string in that position. If you need to use it as a different data type (ex: integer) then you can do an explicit type conversion."

Not sure exactly what you mean here. The protocol and libpq support sending literal values entirely outside of the query itself. There is no reason for the DB driver to do any parsing.

Re: Simple API with Nginx and PostgreSQL

#43

Post by Ixiaus »

bdcravens wrote: post #37

Doesn't couchdb accomplish this? (out of box REST API with little config)

CouchDB's support for "views" and using comet/websockets (have they implemented websockets yet?) to communicate with a JS app served from the DB itself...

Riak does too - any DB that implements a RESTful interface to the database instead of a binary protocol.

CouchDB is particularly well-suited for this use-case though (I would never use Riak in that fashion).

Re: Simple API with Nginx and PostgreSQL

#46

Post by sehrope »

jeffdavis wrote: post #42
sehrope wrote: post #34

Earlier quoted context omitted.

Unfortunately when defaults are inherently insecure, they lead to people building insecure systems. If every single parameterized SQL command needs to include (possibly multiple) escapes then it'll be missed in some places. It's unnecessary anyway. It would be way better would be if the parameters were bound as named parameters. Ex: location ~ /articles/(? \d+) { postgres_pass database; rds_json on; postgres_query HE…

"The DB driver should parse the parameter and bind it as a string in that position. If you need to use it as a different data type (ex: integer) then you can do an explicit type conversion." Not sure exactly what you mean here. The protocol and libpq support sending literal values entirely outside of the query itself. There is no reason for the DB driver to do any parsing.

Miscom on my part. By driver I meant the nginx module that's calling out to libpq (which would more accurately be referred to as the DB driver).

I meant that libpq supports bind variables and the nginx module should be using them rather than performing a string substitution.

Re: Simple API with Nginx and PostgreSQL

#47

Post by thezilch »

stevoski wrote: post #25

This article gives a good example of why you SHOULD consider using an existing framework to create a REST API. I see no concern about authentication, authorisation, scalability, protection from SQL injection attacks, nor making the output easily parseable by third-party applications. None of these are issues you can simply say, "I'll deal with that later when it becomes a problem." They are reasons why an existing fr…

Frameworks don't magically solve these concerns. I can create my API with Django, and there's no guarantee that I will restrict my endpoints to authenticated parties or I guard article modifications to their authors, for example.

Django doesn't magically scale; you still need to a) learn how their ORM works and where it can be greedy or b) just use a "saner" ORM (eg. SQLAlchemy) or otherwise write your own without the need of a thousand features you don't need.

SQL... set variables with `postgres_escape` and not `set`, always.

Output... he returns appropriate responses, but between all the "RESTful" frameworks I've seen, they ALL have different opinions on what should be returned, what HTTP codes to use, etc. I'm not sure a framework helps, other than to inform you or get you stuck with their ridged paradigms.

Re: Simple API with Nginx and PostgreSQL

#49

Post by eksith »

euphemize wrote: post #39

Great for a quick little hack, but I wouldn't want to maintain that conf file once the project grows.

Well, this is for a very simple REST API and the post acknowledges that much. I mean if this does become a "project", I'm sure it's time to move it to a proper framework, but as you say for a quick hack, it's a pretty nice way to do it (with proper sanitization, of course).

I'd also venture that this is an order of magnitude faster and more responsive than a framework so there are definitely some plusses and minuses to consider.

Post reply on HN