To everybody who’s interested and has seen those flashy ipage.com advertisements.
If you are thinking of signing up with them, beware - they do not allow remote connecting to a user’s database.
How crap is that!
Just saying,
Sean
Thats mainly a security feature. A lot of people prefer querying their databases through an intermediate web service. It’s more work however. If its a MySQL instance you can just upload an instance of phpMyAdmin to manage your database if you cannot connect directly.
Yes - but if you’re interesting in building a Xojo app that wants to access a database it’s useless.
The goal is not to use phpadmin at all!
Webhosting.net is the best solution for me!
Not quite. It just means you need to use a local database for development to mimic what the remote one will look like. When you push the site live you can use something like phpMyAdmin to issue sql statements for any schema changes you made. You can also do the same in the Xojo app by keeping track of your database version.
This scenario is almost exactly like using a local SQLite database on a desktop application. You the developer can’t access it so you have to manage it through code.
yeah - but you can’t write to it from the app.
Yeah you can, you can access the database from the local machine. Just not from a remote machine. So the local web app does all the database setup/upgrading/etc.
Sorry - I meant to clarify - you can’t access it from a desktop app.
Right. And the point is, that you shouldn’t. It’s a major frickin’ security risk. Don’t do it. Ever. Use a WE app, not a Desktop app. Your Desktop app can communicate with the WE app, though, to get the data in a secure manner.
Tim, I am curious how this is such a security risk? I’ve heard this but what are the details? I can SQL injection, but those can be mitigated. I’d like to understand this more.
For example a desktop app connected to an SQL Database hosted on Windows Azure:
The database connection requires a password, encryption and certificate. Where does the security risk lye? Is it with the desktop app or from a third party trying to “break in”? Even if my application resides on the server and it is the intermediary my database is still vulnerable to a third party outside attack if the password is compromised.
Many desktop APPs are connected to remote databases so data can be synchronized between devices or utilized by multiple users. Reading the docs on Azure SQL it looks pretty straight forward, especially with ODBC. What am I missing?
[quote=42854:@Joseph Evert]Tim, I am curious how this is such a security risk? I’ve heard this but what are the details? I can SQL injection, but those can be mitigated. I’d like to understand this more.
[/quote]
If the database is directly open to connections on the wider network, an attacker is just a username and password away from taking complete control of your database. An attacker might get these by sniffing a connection if this connection is not secured by SSL/TLS or by decompiling your application’s code. End of story.
When you add some middleware, such as PHP scripts that do specific actions on the database, or a Web Edition application with “special” URL handlers that do specific things, you limit the damage a clever outsider can do to the functions those scripts or special URLs expose.
Yes, I can see where this is a vulnerability. Azure does require SSL so the immediate concern is the decompile of the application to acquire username and password.
I understand the concept of this, however not the implementation. I don’t see where you can provision an Azure database and specify that it cannot be seen by the outside world, in fact their purpose is the exact opposite. They are advocating username, password and SSL. In my digging, I don’t see any discussion on using a middleware app to pass data back and forth from the database to the calling app, just the opposite, their examples are not using anything other than username and password. More info here
In the concept of having a middle layer; what prevents a user from monitoring the traffic your local app sends to the middle tier and just replicate that. For example if your app sends a command to delete a record from the DB to the middleware, is it not possible for someone to hijack that method to damage the database? Where can I read up on this concept?
Thanks.
Joseph, it’s a general safe design rule, not Azure specific. You could construct an app where the user needs to provide database login and password, rather than hardcoding an “app user” into the app. There are pros and cons of such a design.
I don’t have a good link for you, as the middleware thing is just what I consider a “best practice” from a lot of experience and comparing notes with others over the years. My first big database project was on the original PalmOS phones made by Qualcomm. We went right into a corporate Oracle database with an app and a later privacy review (it was sensitive personal data) wasn’t very kind. Doh!. Someone who took our Oracle login out of the app could get sensitive information on a whole lot of people who didn’t sign up to have their information shared like that.
In general with the middleware thing, though, you wouldn’t make low lever functions like “Delete record” directly available. You’d make higher level application-specific actions available. And your script might lock these actions down with a login and session identifier parameter or something like that. The main point, though, is that your database password stays on the server, not out in apps in the field. The middleware should also enforce access policies that a relational database isn’t designed to enforce.
Thanks Brad - good info to consider.
As a counter-point : middleware can itself have security holes, which can increase your overall risk. Imagine a PHP script which uses the ‘eval’ function w/o any sanitization of input. This can give an attacker a big surface of attack.
In conclusion, security is a land of contrasts.
[quote=42913:@Michael Diehr]As a counter-point : middleware can itself have security holes, which can increase your overall risk. Imagine a PHP script which uses the ‘eval’ function w/o any sanitization of input. This can give an attacker a big surface of attack.
[/quote]
Presumably, the purpose of writing the custom middleware for your networked application is to prevent attackers from directly accessing your database and having free reign there. So ensuring that the scripts can’t get hijacked would be part of he plan.
Here’s another way to think of it. Your database is like private methods and properties in a Xojo class. That’s where the meat of work is done, but with good OO design, callers / customers shouldn’t be able to get into the sausage factory. Instead the call the public API. In the case of our hypothetical database backed system, that’s the PHP scripts or WE special handlers. Think of these are similar to public methods and properties a Xojo class might expose.
[quote=42871:@Joseph Evert]Yes, I can see where this is a vulnerability. Azure does require SSL so the immediate concern is the decompile of the application to acquire username and password.
[/quote]
Or direct attacks against known vulnerabilities in whatever server software
CERT tracks these
Brads got some good advice - expose “business processes/functions” not low level commands like “delete row”, “insert row” and that will limit exposure a lot
The upside to this design is that you can also do a lot of backend work (updating servers, providing hot fail overs, etc) without ever having to touch the front end code.
[quote=43061:@Norman Palardy]The upside to this design is that you can also do a lot of backend work (updating servers, providing hot fail overs, etc) without ever having to touch the front end code.
[/quote]
Yahtzee. Or as the kids say today, “This.”