The query works when I replace the WHERE statement with ‘WHERE QB_user.USER_IP = 1;’ But how do I get it to look up the USER_IP field dynamically from $1
Beware of SQL injections with a query like this. You are directly inserting sql statements, hence you could inadvertently delete your whole table (or a malicious user could).
Also on a more general level, it is not good practice to have your sql database public available, they are prone to hacking, so using an api as gateway is the best way.
EDIT: I just realized my post may not have been that helpfull, so here is a little addendum.
SQL databases run CODE, and hence a query is a code run directly on the database. Queries that seemingly look secure can be made unsecure by hackers by adding simple text like " or “”=" in the statements which makes all statements “TRUE”.
For example, a user with bad intentions could change their username to “My Name DROP TABLE Users”, and then delete your whole user table.
SQL injections are prevented by dividing the statements into two (have a look here: https://www.w3schools.com/nodejs/nodejs_mysql_insert.asp), but i’m not sure if bubble supports this, and cant test it as my databases is locked away which I will comment on in a bit.
It may seem unnecessary, but if you spend enough time on stackoverflow or reddit forums you will see that a lot of users get their sql databases injected with bad code, so be sure to implement safe code from the start.
Regarding the public availability. Your database can hold a lot a valuable information, and although a long password may seem secure, hackers do often get acces to databases that are “public”, meaning databases that have an ip-address that you can connect to. If a hacker get access by brute force or sql injection, you would probably only realize it when it is much too late. They could harvest data, blackmail you, delete everything for “fun” or change data without you knowing. Therefore, in general, try to keep databases locked away from public internet.
Its harder, sure. I spent a lot of time on this, and my setup is a sql database (on Amazon) which is in a private net, and its a pain to setup first time. A “lesser” but still pretty secure setup could be a database that only accepts a certain ip-range, and then connect to it from something like integromat (or a dedicated bubble server).
Thanks @simon7, yep just trying a proof of concept before invest time and money in an API, would make life a whole lot easier if Bubble had a fixed IP then it would be easy to apply an AWS security group while testing - but it is just dummy junk database anyway.
But yes, if it works on my test data and I get the queries sorted I can improve the specification and then most likely use AWS lambda and API gateway.
But agree with what you said, but most will go over the top of Bubble makers and it would be highly useful if Bubble made it easier a lot easier to access or write to external databases.
Great, and your welcome Hope that others using sql directly will reconsider their approach. For testing I agree its fine, but in production its a no-no
Hi @simon7 this is not exactly correct. If you notice the ? placeholders in the query, the parameters aren’t put in by text substitution, but by parameter binding. This is an industry standard and proof against most SQL injection attacks.
I somewhat agree with this… the type of hacking here is brute force password guessing - easily defeatable with long random passwords. But the attempts can end up DOS the database server.
Firewalls are a decent idea, but unfortunately Bubble don’t supply IP ranges, as they have a large dynamic set from AWS.
On the other hand, if the database address is not listed anywhere, it has a low chance of being targetted.
Edit - if DOS problems do occur, could look at a proxy service like Cloudflare that has protection, although I’m unsure if they do cover database protocols
Another edit —
Likely has built-in protection against Denial-of-service (DOS) attacks, nice!
Bubble are aware that an IP range is a much requested feature and if we get this it will certainly make life so much easier for everyone!
But yes I agree also that a database end point only becomes compromised if it is known, but with AWS if you have IP ranges you can put it behind a security group.
Please Bubble team bump this one up, so many of us need ip certainty!
Hi @mishav
I hope you are right that bubble inserts text by parameter binding but the syntax would normally look more like: SET COMMENTS (SOURCE, LOCATION etc.) WHERE QB_USER = ? VALUES ? and then VALUES in an array of its own, completely separated from the sql statement itself.
Have you tried yourself or have confirmation from the team that they do proper parameter binding behind the scenes?
Another problem is that people without a db background easily would miss if a breach had happened. Without proper key rotation etc. that breach could remain unfound for long. Agree that an unknown database with a long key could be relatively safe, but if its mission critical its still a big no no to have it public.
Not directly, but yes. Its in a non-public subnet and only accessible by api with a rotated token or ssh through an ec2 instance, so yes, its pretty secure
You’re right to ask the question. Yes I tested this a couple of years ago, but also, you shouldn’t take my word for it, and should email Bubble support.
API’s have a similar attack vector, and potentially the same access to data if used instead of the database plugin.
One big problem though is, the SQL database connector plugin shows the full connection string if the app editor is made publicly viewable! I’m guessing it was missed in the last security audit, lol.
Hi @mishav I would hope that most would have the common sense not to allow public access to the editor with a live database credentials inserted!
But yes agree with your previous comments , just because you are using an API doesn’t make it anymore secure!
Also, you can make the database end point harder to guess by adding a random number or letter string after a name as well as adding a long random password.
I also had a response from Bubble and they acknowledged that have static IP ranges for secure data connection was a very highly demanded request and it is one they have in the priority list. Fingers crossed we may have a solution for us sooner than later.
But even when we are able to lock down the source IP, using the above method to create the database is still advised as is a regular password and end point rotation.
Hope that others using sql directly will reconsider their approach. For testing I agree its fine, but in production its a no-no 
