JDBC Plugin: Difference between revisions

From NSB App Studio
Jump to navigation Jump to search
No edit summary
 
(14 intermediate revisions by the same user not shown)
Line 1: Line 1:
This plugin allows you to execute simple queries against any database with a JDBC driver using Android. Due to the requirement of adding a driver JAR, it's not compatible with automated build services (like PhoneGap Build). You'll need to use [[PhoneGap CLI|PhoneGap CLI]]  
This plugin allows you to execute simple queries against any database with a JDBC driver using Android. Due to the requirement of adding a driver JAR, you'll need to use [[Cordova|Cordova CLI]] instead of VoltBuilder. Since it is based on Java, it is Android only.


The plugin provides a generic interface for using JDBC libraries with a PhoneGap plugin. The interface has a hook to a Java driver which does the actual work. [https://en.wikipedia.org/wiki/JDBC_driver JDBC drivers] are available for many databases.
The plugin provides a generic interface for using JDBC libraries with a Cordova plugin. The interface has a hook to a Java driver which does the actual work. [https://en.wikipedia.org/wiki/JDBC_driver JDBC drivers] are available for many databases.


The main documentation and files for the plugin are here: https://github.com/arsmentis/cordova-plugin-jdbc. In this document, we will discuss how to use the plugin from AppStudio.
The main documentation and files for the plugin are here: https://github.com/arsmentis/cordova-plugin-jdbc. In this document, we will discuss how to use the plugin from AppStudio.


Begin by making sure the PhoneGap CLI toolchain is installed properly. Documentation for installing it is in the Install Tools section of this page: [[PhoneGap CLI|here]].  
Begin by making sure the Corodova CLI toolchain is installed properly. Documentation for installing it is in the Install Tools section are [[Cordova|here]].  


=== Creating your AppStudio Project ===
=== Creating your AppStudio Project ===
Next, create a new project in AppStudio. You'll need to do a couple of extra steps:
Next, create a new project in AppStudio. You'll need to do a couple of extra steps:


1. Add the hook to configxml: Open configxml in Project Properties. Add the hook declaration just after <platform name="android">
1. Add the hook to configxml: Open configxml in Project Properties. Add the hook declaration just after <code>&lt;platform name="android"></code>
<pre>
<pre>
&lt;platform name="android">
&lt;platform name="android">
Line 18: Line 18:
</pre>
</pre>


2. Go into Preferences/PhoneGap and set 'Build Command' to cordova build.
2. Go into Preferences/VoltBuilder and set 'Build Command' to <code>cordova build</code>


3. Save your project.
3. Save your project.


4. From the run menu, choose Make Native App with PhoneGap CLI.
4. From the run menu, choose Make Native App with Cordova CLI.


=== PhoneGap ===
=== Cordova ===


AppStudio should now have created a phonegap folder in your project directory. This will contain all the build files for PhoneGap CLI. Each time you choose Make Native App with PhoneGap CLI, these files will be updated with the latest version of your code.
AppStudio should now have created a cordova folder in your project directory. This will contain all the build files for Cordova CLI. Each time you choose Make Native App with Cordova CLI, these files will be updated with the latest version of your code.


1. If you get a message in the Build window which says "No platforms add to this project", open up a cmd window in the phonegap folder and add Android as a platform:
1. If you get a message in the Build window which says <code>No platforms added to this project</code>, open up a cmd window in the cordova folder and add Android as a platform:
<pre>
<pre>
cordova platform add android
cordova platform add android
</pre>
</pre>


Repeat Make Native App with PhoneGap CLI.
2. Add the plugin: From a command window in your cordova folder, enter
<pre>
cordova plugin add cordova-plugin-jdbc
</pre>
 
3. Repeat Make Native App with Cordova CLI.


2. Add the following folders to the phonegap directory.
4. Add the following folders to the cordova directory.


* spash: your splash screens.
* spash: your splash screens.
Line 59: Line 64:
</pre>  
</pre>  


3. To check if the PhoneGap toolchain is OK, by using the requirements command:
5. To check if the Cordova toolchain is OK, by using the requirements command:
<pre>
<pre>
> cordova requirements
> cordova requirements
Line 69: Line 74:
</pre>
</pre>


3. If the build is successful, the apk file will be in phonegap/platforms/android/build/outputs/apk.
6. If the build is successful, the apk file will be in <code>cordova/platforms/android/build/outputs/apk</code>


=== Example (BASIC) ===
== Example ==
<pre>
 
<tabber>
JavaScript=
<syntaxhighlight lang="JavaScript">
Button1.onclick = function() {
    jdbc.load("com.ibm.jtopenlite.database.jdbc.JDBCDriver", loadSuccess, loadFail);
};
 
function loadSuccess(data) {
    console.log("success", data);
}
 
function loadFail(data) {
    console.log("fail", data);
}
</syntaxhighlight>
|-|
BASIC=
<syntaxhighlight lang="vb.net">
Function Button1_onclick()
Function Button1_onclick()
   jdbc.load("com.ibm.as400.access.AS400JDBCDriver", loadSuccess, loadFail)
   jdbc.load("com.ibm.jtopenlite.database.jdbc.JDBCDriver", loadSuccess, loadFail)
End Function
End Function


Line 84: Line 107:
   console.log("fail", data)
   console.log("fail", data)
End Sub
End Sub
</pre>
</syntaxhighlight>
</tabber>


=== Example (JavaScript) ===
=== Security ===
<pre>
Button1.onclick = function() {
    jdbc.load("com.ibm.as400.access.AS400JDBCDriver", loadSuccess, loadFail);
};


function loadSuccess(data) {
Cordova apps are generally not difficult to decompile. This means that your database host, name, user, and password could easily be exposed by a knowledgable person if your app is public. If you use this plugin to access sensitive data, it's very important you restrict the rights of the database user so they can only perform the bare minimum of tasks needed for the app to function. You should assume that curious, or perhaps malicious people may connect to your database without using your app. Secure your data accordingly.
    console.log("success", data);
}


function loadFail(data) {
Additionally, the <code>execute</code> method has no protection against SQL injection. Be sure to sanitize your input appropriately for the underlying database.
    console.log("fail", data);
}
</pre>

Latest revision as of 16:09, 10 November 2020

This plugin allows you to execute simple queries against any database with a JDBC driver using Android. Due to the requirement of adding a driver JAR, you'll need to use Cordova CLI instead of VoltBuilder. Since it is based on Java, it is Android only.

The plugin provides a generic interface for using JDBC libraries with a Cordova plugin. The interface has a hook to a Java driver which does the actual work. JDBC drivers are available for many databases.

The main documentation and files for the plugin are here: https://github.com/arsmentis/cordova-plugin-jdbc. In this document, we will discuss how to use the plugin from AppStudio.

Begin by making sure the Corodova CLI toolchain is installed properly. Documentation for installing it is in the Install Tools section are here.

Creating your AppStudio Project

Next, create a new project in AppStudio. You'll need to do a couple of extra steps:

1. Add the hook to configxml: Open configxml in Project Properties. Add the hook declaration just after <platform name="android">

<platform name="android">
  <hook type="after_prepare" src="scripts/copyDriver.js" />
  ...
</platform>

2. Go into Preferences/VoltBuilder and set 'Build Command' to cordova build

3. Save your project.

4. From the run menu, choose Make Native App with Cordova CLI.

Cordova

AppStudio should now have created a cordova folder in your project directory. This will contain all the build files for Cordova CLI. Each time you choose Make Native App with Cordova CLI, these files will be updated with the latest version of your code.

1. If you get a message in the Build window which says No platforms added to this project, open up a cmd window in the cordova folder and add Android as a platform:

cordova platform add android

2. Add the plugin: From a command window in your cordova folder, enter

cordova plugin add cordova-plugin-jdbc

3. Repeat Make Native App with Cordova CLI.

4. Add the following folders to the cordova directory.

  • spash: your splash screens.
  • icons: your icons.
  • libs: Put your JDBC driver here. It will be called something like jtopenlite.jar.
  • scripts. Add a file called copyDriver.js to it with the following contents:
var fs = require('fs');
var path = require('path');

module.exports = function(context) {
  var libsPath = path.join(context.opts.projectRoot, 'libs');
  var platformLibsPath = path.join(context.opts.projectRoot, 'platforms',
                                   'android', 'libs');
  var libs = fs.readdirSync(libsPath);

  libs.forEach(function (lib) {
    console.log('Copying libs/%s to platforms/android/libs...', lib);
    fs.createReadStream(path.join(libsPath, lib))
      .pipe(fs.createWriteStream(path.join(platformLibsPath, lib)));
  });
};

5. To check if the Cordova toolchain is OK, by using the requirements command:

> cordova requirements
Requirements check results for android:
Java JDK: installed .
Android SDK: installed
Android target: installed android-22,android-23
Gradle: installed

6. If the build is successful, the apk file will be in cordova/platforms/android/build/outputs/apk

Example

Button1.onclick = function() {
    jdbc.load("com.ibm.jtopenlite.database.jdbc.JDBCDriver", loadSuccess, loadFail);
};

function loadSuccess(data) {
    console.log("success", data);
}

function loadFail(data) {
    console.log("fail", data);
}

Function Button1_onclick()
  jdbc.load("com.ibm.jtopenlite.database.jdbc.JDBCDriver", loadSuccess, loadFail)
End Function

Sub loadSuccess(data)
  console.log("success", data)
End Sub

Sub loadFail(data)
  console.log("fail", data)
End Sub

Security

Cordova apps are generally not difficult to decompile. This means that your database host, name, user, and password could easily be exposed by a knowledgable person if your app is public. If you use this plugin to access sensitive data, it's very important you restrict the rights of the database user so they can only perform the bare minimum of tasks needed for the app to function. You should assume that curious, or perhaps malicious people may connect to your database without using your app. Secure your data accordingly.

Additionally, the execute method has no protection against SQL injection. Be sure to sanitize your input appropriately for the underlying database.