CRYPTO API

GTG2180

REST ARCHITECTURE’S KEY POINTS

The idea is that when you access a web resource (either using a browser or a specific client application) the server will send you a representation of the resource (an HTML page, an image, some raw data etc.).
The client receiving the representation is set in a given state. As the client accesses further information or pages (maybe using a link) its state will change, transferring from the previous one.

The idea of a web service is rather abstract. When it comes to technologies, there are currently two main solutions that are attracting developers.
One is the use of the Simple Object Access Protocol (SOAP) referenced at the site at http://www.w3.org/TR/soap/.
Another web service solution is the use of a Representational State Transfer (REST) approach.

The term REST, an acronym for Representational State Transfer, was originally coined by Roy Fielding in his Ph.D. dissertation in year 2000, and spread very rapidly as synonymous with accessing data over the web using HTTP and URLs, rather than relying on the SOAP standard.

If REST is the architecture (or even better, an architectural style) it is clearly not a standard, although it uses several existing standards like HTTP, URL, plus many format types for the actual data.
In contrast to SOAP, REST architectures use HTTP and its data format (generally XML or JSON) exactly as they are:

REST uses URLs to identify a resource on a server (while SOAP uses a single URL for many requests, detailed in the SOAP envelope). Notice the idea is to use the URL to identify a resource not an operation on the resource.
REST uses HTTP methods to indicate which operation to perform (retrieve or HTTP GET, create or HTTP PUT, update or HTTP POST, and delete or HTTP DELETE)
REST uses HTTP parameters (both as query parameters and POST parameters) to provide further information to the server
REST relies on HTTP for authentication, encryption, security (using HTTPS)

Marco Cantu

Support

STANDARD FUNCTIONS
(MySQL crypto functions)

ConnectToMySQL(Address, Port, Dartabase, User, Password)

Address – String;
Port – Integer as string;
Database – Srting;
User – String;
Password – String.

Note: In this function, the address and port are the address (IP address or URL) and port of the MySQL server.

Example:

REST Crypto Server (RCS)

IP address or URL - 192.168.1.2
REST Server port - 8080
DS Context - eoquant
REST Context - rest
Server methods - TMaster
Server function - ConnectToMySQL

MySQL Server

My SQL server IP address - 100.100.100.100
MySQL server port - 3306
Database name - MyDB
MySQL Username - UserName
MySQL Password - MyPassword

IMPORTANT!

REST server and MySQL server parameters are conditional.

When working with REST Crypto Server (RCS), it is necessary to replace them with real ones. Otherwise, the expected results will not be obtained.

Query:

192.168.1.2:8080/eoquant/rest/TMaster/ConnectToMySQL/100.100.100.100/3306/MyDB/UserName/MyPassword

Result:

{"result":[true]}

FPS - encrypt using control string

DisconnectFromMySQLDatabase(UserName, Password)

UserName – String;
Password – String.

Example:

192.168.1.2:8080/eoquant/rest/TMaster/DisconnectFromMySQLDatabase/UserName/MyPassword

Result:

{"result":[true]}

SetCryptoMode(EncryptionId)

EncryptionId's code is of Word type. Depending on the set REST, the server activates the execution of the following cryptographic functions as follows:

StrongDecryption - EncryptionId = 1;
StrongEncryption - EncryptionId = 2;
Encryption and decryption (default) - EncryptionId = 3.

Note: It is recommended to use the default encryption method. In a number of cases, this function is used when encryption is done using Database Encryption System (DES), and the user works only with an encrypted database.

Example:

EncryptionId = 3.

192.168.1.2:8080/eoquant/rest/TMaster/SetCryptoMode/3

Result:

FPS - encrypt using control string

ExecuteQuery(Query)

Query – String.

Example:

Input data:

IP address or URL - 192.168.1.2
REST Server port - 8080
DS Context - eoquant
REST Context - rest
Server methods - TMaster
Server function - ExecuteQuery
Query - SELECT * FROM cargus_cities LIMIT 20

Result query string:

192.168.1.2:8080/eoquant/rest/TMaster/ExecuteQuery/SELECT * FROM cargus_cities LIMIT 20

Result:

FPS - encrypt using control string

ResultAsJSON(Query)

Query – String.

Example:

192.168.1.2:8080/eoquant/rest/TMaster/ResultAsJSON/SELECT * FROM cargus_cities LIMIT 20

Result:
FPS - encrypt using control string

ResultAsText(Query)

Query – String.

Example:

192.168.1.2:8080/eoquant/rest/TMaster/ResultAsText/SELECT * FROM cargus_cities LIMIT 20

Result:
FPS - encrypt using control string

ResultAsHTM(Query)

Query – String.

Example:

192.168.1.2:8080/eoquant/rest/TMaster/ResultAsHTM/SELECT * FROM cargus_cities LIMIT 20

Result:

FPS - encrypt using control string

ResultAsTextTable (Query)

Query – String;
ColumnsWidth - Integer.

Example:

192.168.1.2:8080/eoquant/rest/TMaster/ResultAsHTM/SELECT * FROM cargus_cities LIMIT 20/50

FPS - encrypt using control string

ResultAsCSV (Query)

Query – String.
Delimiter - String;
Enclosure - String;
Header - Boolean.

Example:

Input data:

IP address or URL - 192.168.1.2
REST Server port - 8080
DS Context - eoquant
REST Context - rest
Server methods - TMaster
Server function - ResultAsCSV
Query - SELECT * FROM cargus_cities LIMIT 20
Delimiter - |;
Enclosure - -;
Header - TRUE.

Result query string:

192.168.1.2:8080/eoquant/rest/TMaster/ResultAsCSV/SELECT * FROM cargus_cities LIMIT 20/|/-/TRUE

Result:

FPS - encrypt using control string

In addition to the standard functions, there is also a large set of specialized ones.
You can find more information about them in the official documentation.

Example:

ResultAsJSONExt (Query, EncodingType, Format, DecodeStatus)

Query – String;
EncodingType - Word;
Format - Word;
DecodeStatus - Word.

MetaData.ResponseContentType:= 'text/plain; charset=utf-8';
// MetaData.ResponseContentType:= 'application/json; charset=utf-8';

Specifies what code table will be used when displaying the content of the result of the query execution. For example:

Content Type: text/plain; charset=utf-8
or
Content Type: application/json; charset=utf-8

Result query string:

192.168.1.2:8080/eoquant/rest/TMaster/ResultAsJSONExt/SELECT * FROM cargus_cities LIMIT 20/0/1/2

Support

USING JAVA SCRIPT

Example:

JS Code

function onReverseStringClick()
{
if (loginRequired && (AdminInst == null))
{
showLogin(true);
return;
}
var QueryField = document.getElementById('QueryField');
var ReseltField = document.getElementById('ResultField');

var s = serverMethods().ResultAsJSON(QueryField.value);

ResultField.value = s.result;
}

HTML Query Edit Fiels

USING PHP SCRIPT

Example:

// 1. EXECUTE MySQL QUERY AND RETURN THE RESULT AS A STRING

function execute_query($mysql_query)
{

// 1.1. Set the REST server the server method and function to use ...
$rest_query = "http://192.168.1.12:8080/datasnap/rest/TMySQLData/ResultAsTextTable/";

// 1.2. Transform the input query as per RFC 3986 requirements (http://www.faqs.org/rfcs/rfc3986.html) ...
$mysql_query = rawurlencode($mysql_query);
$rest_query = $rest_query.$mysql_query;

// 1.3. Get the results of the query execution as a string ...
return file_get_contents($rest_query);

}

$query = "SELECT * FROM cargus_cities LIMIT 20";

echo execute_query($query);

Note: More information can be found in the relevant section of the technical documentation.

Support

ENCRYP AND DECRYP MySQL DATABASE

Encryption and decryption of databases when using FPS Crypto API is carried out with the name of the field enclosed by crypto tags in the SQL request.

By default, these tags are

[ filed name ] for data encryption

and

{ field name } for data decryption,

but they can be modified according to specific needs.

Example

Let's look at a table where we have both encrypted and unencrypted fields.

FPS - encrypt using control string

If we execute a SQL request of the type

SELECT
name_en,
address,
business_email,
id_type
FROM clients LIMIT 20

the result will be three encrypted and one unencrypted fields.

FPS - encrypt using control string

If we execute a SQL request of the type

SELECT
{name_en},
address,
business_email,
id_type
FROM clients LIMIT 100

The name_en field will be displayed in decrypted form.

IMPORTANT!

Although the result is visualized in decrypted form, in the database this field is in encrypted form.

Decryption is performed only for the request execution report.

To indicate that the name_en field should be decrypted we have used the corresponding tags.

FPS - encrypt using control string

If we want an unencrypted field to be displayed in an encrypted form in the report of the implementation of the SQL, the appropriate tags must be used.

FPS - encrypt using control string

In the SQL request

SELECT
id,
status,
[client_type],
client_specific,
profile_type,
is_confirmed
FROM clients LIMIT 100

the [client_type] field will be displayed in encrypted form, even though it is not encrypted in the database.

IMPORTANT!

The display of encrypted fields is determined by the corresponding function. In all examples it is in hexadecimal.

Support

CIPHER AND HASH FUNCTION

In the process of standard encryption of MySQL, it is necessary to respect the imposed system restrictions.

Information about them can be found in the official documentation of the MySQL section 12.14 Encryption and Compression Functions (https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html).

In this case, the possible crypto primitive combinations are reduced to:

For ciphers:

AES Cipher;
RSA Cipher;
DES Cipher.

Key length in bytes:

AES -16, 24 or 32 Bytes;
RSA - 128, 256, 512 Bytes;
DES - 128, 256 or 384 Bytes.

For hash functions:

MD5;
SHA1;
SHA2.

To them we can add the cipher IV (Initialization Vector, https://en.wikipedia.org/wiki/Initialization_vector).

FPS - encrypt using control string

In InnoDB, the actual encryption and decryption is done at the I/O level. That is, the page is encrypted just before it is flushed to disk and decrypted immediately after being read from disk.

In InnoDB, the actual encryption and decryption is done at the I/O level. That is, a page is encrypted just before it is flushed to disk and decrypted immediately after it is read from disk. In InnoDB, encryption only works at the tablespace level. And by default, all tables are created in separate tablespaces (file-per-table tablespace). In other words, a tablespace is created that can contain only one table. Although you can also create tables in the main tablespace (general tablespace). But in any case, the table is always in some tablespace. And since encryption is done at the tablespace level, it is either fully encrypted or not. That is, you cannot encrypt only part of the tables in the main table space.

If for some reason you have file-per-table disabled, then all tables are created inside the system tablespace. In Percona Server for MySQL, it is possible to encrypt the system tablespace using the innodbsystablespaceencrypt variable or using encryption threads, but this is still an experimental feature. MySQL does not have this.

FPS - encrypt using control string

To improve crypto-protection, a system including a master key and converters (envelope encryption) can be used, but it also has both advantages and disadvantages, which are the subject of separate analyzes.

The idea behind envelope encryption is that the keys used for encryption (tablespace keys) are encrypted with another key (the master key). Tablespace keys are actually used to encrypt data.

Graphically, this can be represented as follows:

FPS - encrypt using control string

The best results are achieved when combinations of different data encryption methods are used (hybrid solutions), guaranteeing a high degree of cryptoresistance.