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 framework is helpful.
Any example of a web application using a database should NOT be using string concatenation for adding field values to an SQL statement. Ever. Really.
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.
Please make a little effort and expand on why this article is interesting instead.
For instance, people are using micro frameworks for a reason. They want less clutter and a more direct grab on what the machine is doing. With the proposed theoretical solution we get to remove one big complexity element in the setup, it is very interesting.
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/(?<id>\d+) {
postgres_pass database;
rds_json on;
postgres_query HEAD GET "SELECT * FROM articles WHERE id = $id";
postgres_rewrite HEAD GET no_rows 410;
}
See how $id has no quotes around it? 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. That's how you prevent SQL injection.
"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.
Well, it would appear to me this is one of the few posts on OpenResty. I recently discovered it myself as one of the faster options on the the TechEmpower Benchmarks. I know that this will lead to a benchmark flamewar, but that is where I first heard of it and started researching it.
However, you might also notice that this project uses Lua (specifically LuaJIT) to run embedded in the server. I would think this avoids the problems partially. There is even a new MVC project budding out of this for Lua/Moonscript called Lapis, if that really assuages your concerns. Seems pretty micro, so I am not sure it is meant to really handle your security concerns. It was used on a demo site for a web game shop site that made it here a few months ago. Check it out; it is pretty cool.
As pointed out before, few frameworks I have researched care about security. Ironically, I think web2py is the only I remember having dedicated page space on doing OWASP evaluation against their code base from a long time back: http://www.web2py.com/book/default/chapter/01#Security). Others rarely mention it, and are total crap security wise. As others pointed out, frameworks are little guarantee against security unless, like the language and dev experience itself, prepare to know what the hell you are doing.
Final point, a much cooler complement might be the "Web Application Firewall" projects built into the web server itself to block typical injection attacks. NAXSI, specifically for Nginx, is a cool project and I will look into soon. Maybe this will interest you to.
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 framework is helpful.
Any example of a web application using a database should NOT be using string concatenation for adding field values to an SQL statement. Ever. Really.