0

We have a remote server containing a SQL MariaDB. I have to write a piece of code to be placed in that same server whose mission is to execute querys asking for data, modify that data and send it to an external api hosted in another server. When I was shown the DB, it was through ssh commands and entering sql mode inside the server rather than trough code like PHP as I have always done it before. So, my code is to placed in the same server as the DB, brings the data, modifys some info and calls the api to upload it.

As I said, I am completely lost so my question is simple: can this be achieved? if so, how? I've read about ssh_connect and exec, but since the code will be placed in the same server I don't think this is necessary, correct me if I am wrong. I can't place any code since I don't know how to start.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Berny
  • 121
  • 14
  • 2
    You can achieve that. Take a look [here](https://www.thegeekstuff.com/2017/05/php-mysql-connect/). BUT be carefull with SQL injections. You can read about it [here](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) – N3x Feb 13 '20 at 09:04
  • 1
    You don't need SSH or exec to query a mariadb / mysql database from PHP. There are code libraries which do it directly. If you google something like "php get data from mariadb" (or swap mariadb for mysql - they use the same interface). you'll get lots of examples. https://www.thegeekstuff.com/2017/05/php-mysql-connect/ shows how to do a basic query using both the `mysqli` and `PDO` libraries (these are alternative choices. Opinions differ but probably on balance I'd recommend using PDO). – ADyson Feb 13 '20 at 09:06
  • Thank you both, I'll be checking your links right away and provide some info later! – Berny Feb 13 '20 at 09:15
  • I might be also getting it wrong, but I do wonder about this API though. What if the server with db has no php? It is possible. And judging by OP comments about being shown db through ssh, it is a likely, however strange it sounds – Eugene Anisiutkin Feb 13 '20 at 09:18
  • Hey @EugeneAnisiutkin I am now working on creating PDO connections as it was mentioned above. Lucky for me, the server had php and PDO modules both installed. Now I am stuck at an auth problem since my host seems to not be allowed to connect to the mariaDB but as I was reading, it seems that I just need to create a user in the DB. I will be updating as I move forward! – Berny Feb 13 '20 at 09:47
  • Hello again! I've been making progress thanks to your help, but now I face a question. In the examples you shared on how to do PDO querys, it is done like this for the WHERE: `$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');` but the first query I need to use follows this pattern: `select GROUP_CONCAT(DISTINCT source_external_subscriber_id) AS ids FROM cdr` Is this a secure query? Or is there a PDO way to do it? – Berny Feb 13 '20 at 11:45

1 Answers1

0

Thank you guys for all the help, I am closing the question now: All I had to do was to use PDO as a secure way to establish a connection and to prepare and execute the querys. Remember I placed my php file in the same server that hosts the DB and note that I had to create a user and grant permissions to the DB you can find how in one of the comments above or here. Here is the code:

try {
    $conn = new PDO('mysql:host=yourhostserver;dbname=dbname','user','password');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}catch(PDOException $e){
    echo "ERROR: " . $e->getMessage();
}


//Example of query
$stmt = $conn->prepare('SELECT GROUP_CONCAT(DISTINCT source_external_subscriber_id) AS ids FROM cdr');
$stmt->execute();
foreach ($stmt as $row) {
    $string = $row['ids'];
}
Berny
  • 121
  • 14