Where is this Bubble sql update query syntax and database discussion

StevenM · 2020-03-01T22:20:01.535Z

Need help figuring our where this is going wrong

So I am trying to write a query for the bubble sql plugin to update an existing record.

The Primary key is the ‘USER_IP’ and the objective is to simply update the row for the record with the user IP.

Can anyone provide any guidance on this please?

UPDATE QB_user
SET USER_IP = ?, FIRST_NAME = ?, LAST_NAME = ?, EMAIL = ?, MOBILE = ?, LOCATION = ?, SOURCE = ?, COMMENTS = ? 
WHERE QB_user.USER_IP = ?;

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

Parameters
DBConnector

StevenM · 2020-03-01T23:07:21.179Z

Fixed my own sql issue

Reversed everything so the primary key was the last parameter and works, makes sense when you see it.

UPDATE QB_user
SET  COMMENTS = ?,  SOURCE = ?, LOCATION = ?, MOBILE = ?,   EMAIL = ?,  LAST_NAME = ?, FIRST_NAME = ?
WHERE QB_user.USER_IP = ?;

parameter-update

StevenM · 2020-03-01T23:09:58.421Z

Have to add that the documentation for this Bubble plugin is atrocious! In fact the manual / documentation for Bubble needs some serious looking at!

mishav · 2020-03-01T23:17:29.547Z

I bet you just can’t wait to find out some of the weird limitations around running stored procs

StevenM · 2020-03-01T23:33:07.989Z

Can’t wait

simon7 · 2020-03-02T07:20:34.074Z

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).

StevenM · 2020-03-02T08:15:23.763Z

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.

simon7 · 2020-03-02T08:21:09.101Z

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

mishav · 2020-03-03T03:40:56.785Z

simon7:

Beware of SQL injections with a query like this. You are directly inserting sql statements

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.

simon7:

it is not good practice to have your sql database public available, they are prone to hacking

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 —

simon7:

my setup is a sql database (on Amazon)

Likely has built-in protection against Denial-of-service (DOS) attacks, nice!

StevenM · 2020-03-03T04:21:04.690Z

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!

vladlarin · 2020-03-03T04:51:18.259Z

Maybe if we can show to Bubble how many exactly - we can get some traction here

Just saying

https://bwl.zeroqode.com/?item=1583211799908x486982792962375700

simon7 · 2020-03-03T07:12:34.364Z

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?

mishav:

On the other hand, if the database address is not listed anywhere, it has a low chance of being targetted.

Main problem is crawling and/or ip guessing. Most sql providers like azure, aws etc. have a common url syntax, aws uses your-db-name.aws-dbcluster.eu-central-1.rds.amazonaws.com.

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.

mishav:

Likely has built-in protection against Denial-of-service (DOS) attacks, nice!

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

mishav · 2020-03-03T12:51:10.964Z

simon7:

Have you tried yourself or have confirmation from the team that they do proper parameter binding behind the scenes?

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.

simon7:

if its mission critical its still a big no no to have it public.

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.

StevenM · 2020-03-03T19:43:55.498Z

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.

system · 2020-05-10T22:20:01.608Z

This topic was automatically closed after 70 days. New replies are no longer allowed.