security – How to secure my queried data from my local MySQL server on a remote machine with a shared root with an untrusted entity?

I have a remote client computer placed in a server center belonging to an entity that must also own the root of the client computer in accordance with industry regulations. The client machine runs a C ++ program that uses libmysqlclient.so and periodically polls a MySQL server in my office via the Internet.

Because the data is extremely sensitive, I need to protect this data from disclosure during the data transfer process. Nobody is trusted in the host, including its owner who owns the root of the client machine.

At first, the JSON The format of the C ++ program configuration database contains an explicit IP address, port, user, and password. Anyone with the root can read them and use them to connect to our server from the client machine. As the IP address and the port must be disclosed to the host owner who manages the intranet, gateway and firewall in accordance with the regulations, I replace the user and the password by encrypted strings in the configuration and the C ++ program will decrypt them at startup to get the desired result. real user and password to connect to the server.

However, the root user can replace libmysqlclient.so in the system with a modified version so that the user and the password are printed when establishing a connection. Then I change the C ++ program to static link to libmysqlclient.a at the time of compilation.

However, the transferred data is still in the clear. I'm therefore activating SSL on the MySQL server and modifying the user as REQUIRE SSL. So, the attack by the middle man is not supposed to be easy.

For SSL to work, I also need to put ca, client-cert, and client-key files on the client machine. I am wondering if the host owner can capture the encrypted traffic transferred and use these files to decrypt the data without knowing the user name and password of the database.

In addition, is there an obvious vulnerability on the client side after all these configurations? Suppose the network structure and the use of MySQL are not changed.

mysql – Problem with the resolution of Promise in a javascript class

The friends,

In the class below, the roof "console.log (records);" sends me all the records back to the terminal screen.

However, when solving the problem "resolves (records);" the following message appears in the terminal:

C: Project backend node_modules mysql lib protocol Parser.js: 437

        throw err; // Rethrow non-MySQL errors
^

ReferenceError: resolve is not defined

Can any one help me?

Node.JS – Dependencies:
"body analyzer": "1.19.0",
"express": "4.17.1",
"mysql": "2.17.1",

// app / models / FileDAO.js

FileDAO class {

constructor (connection) {
this._connection = connection;
}

listing () {
return new promise ((req, res) =>
this._connection.query (& # 39; SELECT * FROM tb_arq_recebidos; & # 39 ;, err, records) => {
if (err) {
return the rejection (err)
} else {
console.log (logs);
resolves (records);
}

})
)
}

}
module.exports =
// End app / models / FileDAO.js


// app / roads / route1.js

const express = require ("express")
router const = express.Router ()

router.get (& # 39 ;, (req, res, next) => {
const FileDAO = require (& # 39; ../ models / FileDAO & # 39;);
new RAID (req.connection)
.list ()
.then (records => res.json (records))
.catch (next)
});

// end of application / routes / route1.js

mysql 5.6 – Change the recording inserted according to the host name

I have a script that inserts test data into a database. This script is shared by different developers who all use their own s3 bucket. I have a table called s3 with s3bucket column and it will only have one line, the name of their compartment s3.

Is there a way to make a conditional insert in this table for this record?
For example, there are 3 developers and there are s3bucket1, s3bucket2, s3bucket3.

@@ hostname would not work because the IP address may change.

Create a time zone for PHP and MySql UTC

I migrate to a cloud hosting where MySql uses a UTC time zone, I could not run the commands to switch to GMT -3 and the support does not give me any other option.

Today, my requests are recorded in the database with the date / time of the bank itself. I have therefore thought of doing a function or a setting in PHP itself, but without success.

Last attempt

MySQL – Plain index vs Fulltext

Based on the following table

CREATE TABLE Lexicon (
term VARCHAR (100),
TEXT input
)

How would you query the database for items such as terms that are not in their entries? Or terms that are not present in any of the entries?

I thought to create a INDEX FULLTEXT at the entrance but the AGAINST() part of the MEET() query would not allow me to pass links.

Can MySQL full-text indexes be used in this type of problem, or would a simple index with a join be sufficient?

mysql – search help to join tables

I am pretty green in mysql, but a task for which I have completely lost is assigned. I have a unique code table in MySQL, then I still have 6 tables for individual clients. I would like to extract the codes that have a value in cat_code = 'wine & # 39; of each of the 5 clients and item_grp_code = & # 39; 00101 & # 39; of 1 customer since the last customer has no cat_code?

An idea how to do around that?

That's what I've tried until now:

SELECT DISTINCT
items.code, nxr.departmentcode, nxr.itemgroupcode, las.departmentcode,
las.itemgrpdesc, las.desc1, nxr.desc1
DE (
retail_append.items articles
left outer join
retail_append.nxr nxr
ON (items.code = nxr.code))
left outer join
retail_append.las las
ON (items.code = las.code)
WHERE (las.itemgroupcode = & # 39; 00101 & # 39;) OR (nxr.categorycode = & # 39; wine & # 39;);

mysql – The value of the prepared statement is listed as an error in the query string

I've created a WordPress plugin that writes data to a proprietary table.

The query in the log is:

INSERT INTO berichten_devices (device_UUID, article_id) VALUES (% s,% s)

If I take this line and enter it manually via the administrator in the database, replace the first% s with "test". and the second% s by 1:

INSERT INTO berichten_devices (device_UUID, article_id) VALUES ("test", 1)

This is stored in the database without problems. There is also another table that is written from the plugin and works well.

The php responsible for storing the data is as follows:

function insert_artice_read ($ uuid, $ article_id) {
error_log ($ uuid: $ uuid, 0);
error_log (& # 39; $ article_id: & # 39 ;. article_id, 0);

$ query = "INSERT INTO berichten_devices (device_UUID, article_id) VALUES (% s,% s)";
error_log (& # 39; $ query & # 39; 0);
error_log ($ query, 0);
$ query = $ this-> wpdb-> prepare ($ uuid, $ article_id);
return $ this-> wpdb-> query ($ query);
}

The parameter $ this-> wpdb is defined as

$ this-> wpdb = $ wpdb;

The logging displayed is as follows:

dcr-wordpress | [Tue Jun 25 10:45:03.363771 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584]    $ uuid: ABCDEF01-2345-6789-ABCD-9876543210AA
dcr-wordpress | [Tue Jun 25 10:45:03.363776 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584]    $ article_id: 1
dcr-wordpress | [Tue Jun 25 10:45:03.363781 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584]    $ request
dcr-wordpress | [Tue Jun 25 10:45:03.363785 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584]    INSERT INTO berichten_devices (device_UUID, article_id) VALUES (% s,% s)
dcr-wordpress | [Tue Jun 25 10:45:03.364844 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584]    You have an error in your SQL syntax; Consult the manual for your MySQL server version for syntax to use near ABCDEF01-2345-6789-ABCD-9876543210AA & # 39; at line 1 of the request ABCDEF01-2345-6789-ABCD-9876543210AA gemaakt door require (wp-blog -header.php), wp, WP-> main, WP-> parse_request, do_action_ref_array (& # 39; parse_request & # 39;), WP_Hook-> do_action, WP_Hook-> apply_filters, rest_api_loaded, WP_REST_Server-> serve_quest  Inc \ Core \ RestController-> article_retrieved, Prop \ Inc \ Common \ Repository -> insert_artice_read
dcr-wordpress | 192.168.224.1 - - [25/Jun/2019:10:45:03 +0000] "POST / wp / wp-json / prop / v1 / berichten-devices HTTP / 1.1" 200 802 "-" "Mozilla / 5.0 (iPhone; UC iPhone OS 12_3_1 as Mac OS X) AppleWebKit / 605.1.15 (KHTML, as Gecko) Mobile / 15E148 "

From this logging, it is obvious that the SQL syntax has a problem, but this line seems strange to me:

for the correct syntax to use near & ABCDEF01-2345-6789-ABCD-9876543210AA & # 39;

These are the parameters data of the prepared instruction, I would expect to see something like

for the correct syntax to use near% s

I've tried using% i instead of% s for the second parameter, be sure, but there was no difference.

That's the definition of the table:

CREATE TABLE IF NOT EXISTS berichten_devices (
device_UUID VARCHAR (255) NOT NULL,
article_id int (10) NOT NULL,
date timestamp (4) NOT NULL DEFAULT CURRENT_TIMESTAMP (4),

KEY `dev_uuid` (device_UUID)

My question is basically: "What am I doing wrong?"

mysql – according to my last attempt to try to update the customer table from the database 1

I've tried this approach, I seem to miss something
Any advice would be useful.
Thank you in advance.

UPDATE databse2.customers_T
INNER JOIN database1.customers_T
ON database1.customers.customers.id = databse2.customers_T.customers.id
SET databse2.customers_T.customers.id searchkey.id customers.name customers.card
clients.address clients.address2 clients.postal
customers.city customers.region customers.country
customers.firstname customers.email customers.phone
customers.phone2 customers.fax customers.visible
customers.curdate customers.image customers.memodate
= database1.customers_T.customers.id searchkey.id customers.name
customers.card customers.address customers.address2
customers.postal customers.city customers.region
customers.pays customers.firstname clients.email
customers.phone customers.phone2 customers.fax
customers.visible customers.curdate customers.image
ccustomers.memodate

How to import database data to a new host that is running a newer version of MySQL without ssh access?

My current host for an old website has only MySQL 5.0
(serverVersion = 10.2.12-MariaDB-log).

I want to move this website to a host with MySQL 5.5 or 5.6 or 5.7 (depending on the server I'm going to).

But the only instructions I can find to update the database data from 5.0 to 5.6 / 5.7 are run from the command line, requiring ssh access that I do not have.
For example, these are the clearest and best instructions I've found, but I can not use them because, as far as I know, I do not have ssh, I do not quite understand the references ([he?] makes. (for example, he says - no faults "for simplicity" but even if I had ssh, I do not know if I should also use this flag or others.)

I usually use MySQL Workbench to connect to remote databases, but when I connect to this old host via MySQL Workbench, a message appears saying [Workbench] is not compatible with 5.0.
So, for this host, I still used MySQL Workbench to make a backup (which probably means that the backup is not good), or I use the web tool of the host (this is not is not my preference, but it is obviously better).
I've also recently installed HeidiSQL as it seems to be compatible with version 5.0 (I still do not give a warning / error message). So I started making backups and minor changes to the data on this host using HeidiSQL.

The only reason I've continued to use the host that runs MySQL 5.0 is that I have not yet found instructions on migrating data from websites on this server, whether through the online database tool of a hosting provider, MySQL Workbench or HeidiSQL!
All I see is intended to perform step-by-step data upgrades using the command line and / or to upgrade the database server itself.

I need a way to upgrade the data from 5.0 to 5.6 or 5.0 to 5.7, probably in one step, using a database connection tool mistletoe or some other independent method.
I will not have access to any other mySQL server that I migrate to (5.0) and to the server I'm migrating to (5.5, 5.6 or 5.7).

Does anyone know how to do that?

EDIT:

  • I usually choose "Export" in the GUI to export and choose all the tables when I do a database backup. I guess this is identical to a "database dump" that I see referenced everywhere.
    Is it correct? If no, how to generate an appropriate dump file?
  • What "settings" of export should I use when the goal is to upgrade and migrate?
  • I also see some references to the users table. Do I need to perform other exports to fully transfer and upgrade my database to a newer server with a newer version?

mysql – Index each column WHERE?

I tried to turn it on MYSQLI_REPORT_ALL | MYSQLI_REPORT_STRICT and now MySQL tells me:

No index used in the prepared query / statement …

in some of the queries in my application.

Is it really the best practice to index each OR column, I will eventually use it so that MySQL does not complain? It seems a little exaggerated, because there are probably columns where I will only use the ORclause occasionally.