Connecting to a database is one of the most rudimentary tasks a developer might encounter. However, despite appearing as a simple bit of code, database connections can be a source of significant problems and potential security vulnerabilities.
New database connection strategies provide a secure, efficient, managed, and centralized method for handling database connections. webMethods.io Integration, a service from Software AG, helps prevent the inclusion of sensitive security credentials in code, prevent connection errors, and reduce the attack surface available to potential attackers.
This article walks you through the pros and cons of traditional and modern database connection methods. You will also learn how to use Software AG to make worry-free connections to any database with a short hands-on tutorial.
Understanding database connections
Typically, after creating a database, you want to connect it to a client application that reads the data, alters it, and sends new data to store. For a connection to be possible, a database server must provide the details that a client needs to initiate a connection. Some of those details include user authentication and authorization credentials, an endpoint, a port number, and a database name.
A client application’s request contains the database connection details needed to initiate a connection. The data sent between a client application and a database is encrypted to protect the connection from a man-in-the-middle attack.
Traditional database connection issues
The traditional database connection method has inherent security risks and operational inefficiencies.
This method requires the client to handle the database connection, which means the sensitive connection details are included in the source code. The presence of such details in the source code increases the risk of a malicious attacker obtaining them. Alternatively, users might opt to save the credentials in an easily accessible file, which produces additional security vulnerabilities.
The traditional method requires opening a connection from an application and making direct requests to the database server. This process makes the database susceptible to malicious requests and attacks, such as denial of service (DoS) and SQL injection attacks.
Data layer
A data layer holding all information management services ensures data is correctly entered, stored, and retrieved. It keeps data in a simple format easily accessible to client applications, reducing the latency associated with data delivery.
The webMethods data layer also ensures timely data delivery and decreased latency with data operations. Finally, it pushes data to its store uniformly, easing its management, storage, and retrieval.
Another feature of the webMethods data layer is the ability to distribute data simultaneously to different applications. The only requirement is to connect the data source and the consuming app.
Worry-Free database connections with Software AG
Software AG’s webMethods.io Integration service is a robust integration platform as a service (iPaaS), providing the combined capabilities of application integration systems, API management tools, and B2B gateways.
It simplifies connecting to a database with predefined connectors and the ability to build custom connectors. To connect to a database, you simply select the database’s connector, enter the connection details, and save the associated account, which you can use to implement different types of integrations in a workflow.
This integration resolves the challenge of traditional database connections by securely storing encrypted database connections in easily accessible accounts. To integrate a connector into a flow, you simply select a connector and the account you want to use. You can also safely store sensitive connection details in the cloud, minimizing the possibility of an attack using stolen credentials.
With webMethods, you connect different components visually by selecting them, dragging them, and adding a connection arrow. The system handles the backend code, and you only need to select the data you want to pass.
Let’s take a look at the visual connection in action.
Visual connection in action
In this section, you’ll create a simple API and connect it to Software AG using Node.js. Then, you’ll initiate and confirm connections to MySQL, Postgres, and MongoDB databases using Software AG’s visual connection method. The project uses Visual Studio Code running on Windows 10.
Prerequisites
To follow this section, you need the following:
- Node.js installed
- Access to a MongoDB database and a valid connection string
- Access to a Postgres database
- Access to a MySQL database
- A Software AG developer account with access to webMethods.io Integration
Finally, ensure the tables or collections you create for the databases store two string variables: name and score.
Creating a simple API
Start by creating a simple API in Node.js. Open a terminal, navigate to the folder you want to use for the project, and run this command:
npm init -y
Add axios, a promise-based HTTP client for Node.js, by running the command below:
npm i axios --save
Create a file named server.js in the project’s root folder and add the code below to it.
var axios = require('axios');
var data = JSON.stringify({
"name": String,
"score": String
});
var config = {
method: 'post',
url: '<WORKFLOW LINK>',
headers: {
'Content-Type': 'application/json'
},
data : data
};
axios(config)
.then(function (response) {
console.log(JSON.stringify(response.data));
})
.catch(function (error) {
console.log(error);
});
Connect to Software AG
Now you connect to Software AG on webMethods.io Integration.
Start by creating a new project and a new workflow inside it. Then, open the workflow and double-click the green play button in the workspace to add a trigger.
As you are creating an API, add a webhook trigger. Selecting the webhook option gives you a URL that the Node.js API uses to connect to Software AG.
Copy the URL and add it to the server.js file in your Node.js project.
The new server.js file looks like this:
var axios = require('axios');
var data = JSON.stringify({
"name": "Patrick Jones",
"score": "68"
});
var config = {
method: 'POST',
url: '<WORKFLOW WEBHOOK PAYLOAD URL>',
headers: {
'Content-Type': 'application/json'
},
data : data
};
axios(config)
.then(function (response) {
console.log(JSON.stringify(response.data));
})
.catch(function (error) {
console.log(error);
});
Now run the Node.js file to send the payload to the webhook on webMethods Integration. Open a terminal in the Node.js project’s root folder and run the command below:
node server.js
You should receive the following output:
{"message":"Mock payload data set for your workflow. To execute the workflow, please use the live webhook URL <WORKFLOW URL>","code":153}
Navigate back to the webhook on webMethods Integration and click Fetch to view the payload sent by the Node.js API.
Below is the received payload data:
This confirms your API is now connected. After the workflow is completed, you change the node.js code to use the actual webhook URL instead of the payload test one. You can then run the node.js code and see the workflow being executed live (if you enable the option to see this in the workflow settings).
To keep this sample project simple, save the webhook without any authentication.
Next, you’ll connect to the database and save the data sent by the API.
Connect to different databases
On the right sidebar, search for the MySQL connector. Fill in the details and save the account as shown below:
Fill in the database server details and click Save to save the connection account.
On the right sidebar, search for the Database connector. Name your connection account, and from the Database menu, select Postgres. Under Driver Group, select Postgres JDBC Driver. Then, fill in the other fields with your Postgres database connection details and save the connection account.
On the right sidebar, search for the MongoDB connector. Select MongoDB and fill in the form with your MongoDB connection details. Finally, save the connection account.
Creating the data-saving workflow
Navigate to the workflow where you made the webhook and add the three databases as shown below.
After saving the workflow, play it to send the payload data to all three databases.
Here is the workflow execution log:
Below are the inputs passed to MySQL:
The next image shows the inputs passed to Postgres:
Finally, here are the inputs passed to MongoDB:
Now that the three database connectors are working properly, we can test sending data to the databases from the Node.js application by running the application using the following:
node server.js
The image below shows how the workflow changes as the data received by the webhook are sent to the different databases.
That’s it. You have created an API connected to Software AG and made a connection to three databases.
Conclusion
The traditional database connection method is prone to errors and increases the risk of hackers gaining access to your databases.
The new visual connection method using webMethods.io Integration eliminates the problem of typo errors by saving database connections in reusable accounts. It also lowers security risks by providing an API layer for a database that you can use to offer business-focused data APIs instead of generic database APIs, which simplifies development for application developers. It also reduces the attack surface as the database is only accessible through the APIs, which offer more restricted interfaces and validation.
Check out Software AG Cloud to learn more
This article is part of the TECHniques newsletter blog - technical tips and tricks for the Software AG community. Subscribe to receive our quarterly updates or read the latest issue.