Android Question What is the best way today to connect directly to a MySQL data server?

[irda]

Hello everyone,

I'm making my first [B4X] application with a database connection and I've been digging through the posts related to MyQSL connections for a couple of days. There is a lot of information and some of it is outdated so I'm a bit lost on the way to go (SD_SQL, jdbcSQL, jRDC2, DBUtils, httputils...).

I don't know which way is the best, could you give me your opinion?

The requirements I need are:
* Direct connection to a MySQL server.
* I can't use php scripts, java server, etc
* My application is made in B4A but in the future I would like to migrate it to B4i

Thanks to this community.

 

[Albert Kallal]

Unfortantly, this is kind of like how big of a ball of string does one need to reach between two points?
Answer: as big as a ball of string as required!

So, "best" way will depend. In other words, are you on some internal corporate network, or is this data to be used/grabbed when you have ANY internet connection from any location?

So, best now requires a context here. While most web sites often come with MySQL for free, the vast majority of web sites do NOT allow a direct connection to the database server OUTSIDE the web site. So, while I can go say find and buy a book on amazon.com, and that web site can hit/ use/ query the database? You outside from that web site are NOT free to direct connect to the database that drives their web site.

Same goes for you. In SOME cases, you can open up and allow external connections to the database and by-pass the web site. But, now your database is WIDE OPEN to the wild and crazy internet. So, if YOU can direct connect to the database, so can anyone else - including some nasty automated "bots" that do this all day long!!!

Years ago, when I though I was smart, I had SQL server (the MSCE edition - edition before free sql express edition). I decided to open up the ports on my home router, and thus I could now hit the database directly from any place I had a active internet connection. Well, in less then 20 minutes, ALREADY there was bots and somthing attempting to logon to the database.

It looked like this:

User=SA, password = "admin"

User Admin, password=123456780

And so one, in other words, all of the common SA and "admin" combinations, with rather common known logons (the default ones) were automatic being attempted.

For the above reason, then such low cost web hosting includes a database server, but ONLY the web system and code behind that web site can hit and use the database, but NOT external outside connections. As noted, this is actually changing now in favor of by-passing the web site. So, with the rise of hosted SQL (Azure) and others, then we seeing return to allowing direct connections to these databases. However, they often now have restrictions such as ONLY allowing a certain IP address to connect (so, you might have to say first VPN into your company network, and THEN you now look like a valid allowed IP address).

So, best and most easy choice? There is no simple answer until such time you address the security issues, and in MOST cases, a simple opening up of a database server without security to the wild and crazy internet is high risk adventure.

If you have a secure network, or VPN, then you can risk and use the jbdc drivers and direct connect to such database servers, but such direct connections really don't follow the web centric type of system that android is based on. And such direct connections to a database are not very good say for a all day long connection that interacts with the database. (not great failure recovery).

So, say you want to use phones or whatever (android os) to scan inventory or products or whatever on the factory floor? Well, most of your operations should occur local on android, and against some local database on the device. But, you could have some type of "sync" button, and shuffle the data to the server with such a setup. But, a lot of code, a lot of database operations, and what typical makes up a android applcation with lots of data operations?
then such direct connections connect to MySQL/SQL server is not all that robust. Such setups can work, but as I stated, this will depend on your goals.

So, do I use direct connect to sql server + android? yes I do. And I LOVE how simple and easy this was. this setup eliminated the need for me having to setup web server, or any other kind of system. All I needed was a valid network connection, and then direct connection to SQL server. Having saved the trouble nd time of web services, or even having a web server was well worth the limitations. (but, I did spend time building a sync routine android side - and it was based on good number of assumptions for this to work well).

so, tops on this list?
Can you risk opening the database server to the given Android direct network connection? If that connection is not secure, or you want to open up the database server ports directly to the wide open internet? Nope, VERY bad idea!! And in 2004 when I attempted this, the internet was not even close as nasty and ridden with hacking bots and the security concerns like it is today!


Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[KMatle]

This is the reason why most providers do not accept a direct connection. And there's more. An app should ONLY display data and trigger updates but never handle permissions or important stuff inside it. So you need a middleware like php or other languages on a server. Here any important code is running like login, db-connections, etc.

Imagine you have an issue in you app. You can't do anything then as no user is forced to update. On a server you can change the code in a minute (add security, block users, etc.).

 

[Erel]

You can use JdbcSQL in B4A, to directly connect to a remote database.
There is no equivalent option in B4i.

I recommend you to use jRDC2 instead of making a direct connection.

 

[irda]

Thanks for the presentation Albert, I should have explained myself better anyway.

My application scenario is as follows:
* The user collects information, photos... and stores them locally. At the end of the day, the user uploads the information to a server, in this case a MySQL database that does allow external connections. I don't have access to the server so I can't run java programs or php scripts.

These data are exploited by the client through its website, but it is a part of the project that I am not in charge of.

I also don't understand in terms of security what is the difference between attacking your database directly or through the script that connects to it, the end result is the same.

Thanks

 

[aeric]

These data are exploited by the client through its website, but it is a part of the project that I am not in charge of.

What left to you is to know what ways to access the database or server are available to you. The question you asked initially will left only one or two choices. Let say, the only option is by communication through REST API then you have to to use Http method.

 

[josejad]

Hi:

What Erel says in this thread:

The recommended method to connect to remote databases is jRDC2: https://www.b4x.com/android/forum/threads/61801/#content

Connecting to the database directly has several disadvantages:

- Insecure - it will be quite simple for a hacker to get the username and password and directly access the database.
- Hard to maintain - changes to the database design will require updating the app.
- Server and client performance issues - The Jdbc drivers are not optimized for mobile usage.
- Reliability issues due to unstable connectivity.

I don't recommend using it in a consumer app. However if you are creating a small in-house solution then it might be a good fit.

You could get an VPS, run you jRDC2 server there, and connect from it to the mysql server... jRDC2 server and MySql don't need to be on the same server.

 

[Albert Kallal]

what is the difference between attacking your database directly or through the script that connects to it, the end result is the same.

A mount Everest of a difference. While a database is secured by a simple logon?

A web site is battle hardened against such security attacks. Prevention of cross site scriptong will exist, and even often any script modified on the page will also fail. (asp.net does this for example automatic).

and better yet?
You probably need to get a logon to to the web site FIRST.

Next up, while you might attack the web site with a script, you NOT going to get direct use of the database.

And if you direct connect to the database, then you can execute things like change user security, delete tables, or do whatever. A simple hacking of the web site in most cases will NOT let you create, nor run 100% server side (code behind on the web site). In fact, with asp.net the code behind in that site is compiled c# or vb.net (there is NOT even server side code available that you can modify!!!).

Also, database logons don't have robust security in place. So, things like how many password attempts can you do?
A simple logon to a database is not even close to the security that a web site will have.

And with a web site, you NEVER actually hit the web site database directly, do you? So, you be hitting a web method of the site, and IT WILL go connect to the database, get the data based on some web method that you CANNOT change nor modify!

So, having a whole web site between you and the database is a significant deal. This is why then in most cases you build and add some web methods, and your android phone then hits and consumes those web methods. Those web methods thus have VERY good web based security, and does not use nor rely on the database security system. So, you have a huge list of options, such as two factor authentication methods. And those logon systems also have 10+ years of battling those bots and systems that attempt to breach web based security. So, they been in the war, and at the war for many years.

In a nutshell?

The database security system is not appropriate for being exposed to the web. So, that's WHY all these web based systems are built around you adding some web methods that are web based, and return data. But, the code and systems that returns the data is NEVER seen, nor touched, nor even able to be changed by you.

So, even if someone gets a valid working logon to the web site, they are STILL in most cases limited to the web methods you created, and they can't change such web methods and code EVEN if they thwart or gain a valid logon. So even if they crack the logon to the web site, they STILL can't send raw sql commands to the database. And that logon will not let them modify or change the sql. If you direct connect, then the client side gets to make, build and send the raw sql commands. And better yet, they might get a logon to the site (like all customers), but now they going to have to hack away and try gain use of the database. For example, you are NOW logged on to this site, right? But, this message no doubt is stored in a database. You can't now hack away, and I doubt VERY much you can or will figure out how to hit the database system that drives this site, and yet you logged on just like I am! So, in most cases, gaining a logon to the web site gives you very little - and in fact the web site is designed to allow users to logon, but that still don't get or let you hit the database system at all.

With a logon directly to the database? I don't have ANY web based authentication or security systems in place, but only a simple password. And with a direct connection to that database, in most cases I can quite much do anything I want to that database, including deleting tables, or grabbing all rows of data, not just the rows of data that say belong to me, based on that fancy pants authentication that the web server has in place.

In most applications, the database does NOT control who gets to see their own data, but is in fact based on the user logon to the web site, which THEN in turn hits the database and pulls data based on your web based logon and authentication. (so you only still ever see and get your own customer information - not the whole database).

I willing to bet that even amazon on-line works this way. The database is not secured by each logon user. But the web site, and YOUR logon is what most certainly restricts the data you see, can get, and is returned from that database to see things such as your orders, and whatever.

So, security of a database is too lame, too limited, and in most cases a direct connection to a database gets you in, and from that point on, little if any levels of security, or EVEN the ability to restrict what rows of data you pull and see does not exist. You be able to execute a query against that database, and it not going to out of the blue restrict rows that JUST belong to you, but you have full use of the tables and data - all data, not just what your supposed to get and see based on your web logon.

In effect, such a setup has little security, but worse has next to no means to restrict a query to ONLY get data based on your logon. And above is the short version of how significant the two approaches to security are. As noted, due to this, few systems or web sites allow one to by-pass the whole web site and direct connect to the database that drives that site.

So, a good question. And as above shows - there is a significant difference here between a direct connection to a database vs that of connection or logging on to a web site. And even most web method calls are going to use, include and rely on the web based security systems in place. And even then, those web methods go get the data - no you, nor a direct query sent from your phone. With a direct connection, then your android is sending the sql commands directly to the database. With a web method, the code behind go gets the data, and returns it to the client side - you never really sending any direct sql commands to the database, but asking the web site to please go get some customers based on a get customers web method. You only get to see the results, not the actual code and SQL used for web method.

so with a direct connect, you don't really get much of any means to restrict data to the one user - you have in most cases full use of the database, and all tables and all rows of data.
Opening up a database to direct external wild internet connections? Simply a non starter choice in my view.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[Spavlyuk]

I also don't understand in terms of security what is the difference between attacking your database directly or through the script that connects to it, the end result is the same.

Applications can be easily reverse-engineered, exposing the credentials to your database. The attacker could then do pretty much anything, such as harvest your data, store (illegal) content and potentially delete all data, without having to interact with your application.

However, if an api is exposed, the attacker will not be able to execute arbitrary commands but only those which you have specified. Even if you trust the users of your application, there's the possibility that their devices or network will get compromised.

If you cannot setup a proxy server to handle requests from your applications, you should probably let management know of the potential risks.

 

[Sifu]

A lot of answer also mine seemed to be removed. Trying to explain the dangers of leaving a database open to the world wide web and the use of middleware. Why?

 

[hibrid0]

This post sound like. I need to build a bridge from point A to B. The traffic is just many Sedan cars the restricction I can only use cardboard.

 

[edgar_ortiz]

This post sound like. I need to build a bridge from point A to B. The traffic is just many Sedan cars the restricction I can only use cardboard.

I agree with @aeric the best option is a communication through REST API. (post #6)

 

[netsistemas]

i create a local database, and update this database to a ftp (or via dropbox). (i recomend use ftp)
In server, i have a program to detect and extact this datafile to a sql server database.
other proyect, i connect directly to sql server (but the server are in other network of the company: permision for only one user, only connect between 08:00 and 20:00, etc)