0

So I have created a basic Watson Assistant Chatbot and a basic MySQL Database and I want to connect them together, for the Watson Assistant to extract something from it.

Here's the scenario as follows:

The Watson Assistant will ask the user to authenticate himself with his Client_ID and his password, if the Chatbot recognizes these credentials, it should great the user by his first name.

ex: "Welcome <first_name(from MySQL DB that I created)> how my I help you?".

Note that my MySQL DB contains these columns: client_id, first_name, last_name, password, email. I couldn't find anything none other than people using IBM Cloud resources such as DB2 which are not free to use.

I even tried this php code in the IBM Cloud Functions to provide a webhook:

<?php
function main(array $args) : array {

    // Retrieve client_id from Watson Assistant
    $clientid = $args['clientid'];

    // Set up MySQL database credentials
    $host = '';
    $port = ;
    $dbname = '';
    $username = '';
    $password = '';

    // Connect to MySQL database
    $conn = new mysqli($host, $username, $password, $dbname);

    // Check for connection errors
    if ($conn->connect_error) {
        return ['error' => 'Failed to connect to MySQL database: '.$conn->connect_error];
    }

    // Construct and execute MySQL query
    $sql = "SELECT first_name FROM clients WHERE client_id = $clientid";
    $result = $conn->query($sql);

    // Check for query errors
    if (!$result) {
        return ['error' => 'Failed to execute MySQL query: '.$conn->error];
    }

    // Retrieve result and return as array
    $row = $result->fetch_assoc();
    if ($row) {
        $response = ['first_name' => $row['first_name']];
    } else {
        $response = ['error' => 'Client ID not found in database'];
    }
    return $response;
}
?>

it showed this error:

{ "error": "The action did not return a dictionary or array." }

Please if there's a solution for my problem or if any other way to do what I asked for I would much appreciate it.

I tried everything and I got nothing.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Youssef Ben Osman
  • 1
  • 2
  • 1
    Your script is open to [SQL Injection Attack](http://stackoverflow.com/questions/60174). Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187) You should always use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's instead of concatenating user provided values into the query. Never trust ANY user input! This will also remove the unescaped character issue like a `'` in a text string like `O'Neal'. – RiggsFolly Feb 21 '23 at 15:16
  • I suppose the first question shoudl be "What form does `$clientid` take"? – RiggsFolly Feb 21 '23 at 15:21
  • @RiggsFolly it's an int – Youssef Ben Osman Feb 21 '23 at 15:25
  • Well in that case its the wrong int, because the query is not returning any resultset – RiggsFolly Feb 21 '23 at 15:26
  • @RiggsFolly do you have any solution? – Youssef Ben Osman Feb 21 '23 at 15:27
  • _Although I would be tempted to add some error reporting_ If you are using PHP < 8.0, to get errors out of PHP _even in a LIVE environment_ add these 4 lines **temporarily, while debugging**, to the top of any `MYSQLI_` based script you want to debug `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);`. This will force any `MYSQLI_` errors to generate an Exception that you can see on the browser as well as normal PHP errors. ___Just in case there is a real error somewhere___ – RiggsFolly Feb 21 '23 at 15:27
  • This is the IBM Cloud solution tutorial showing how to integrate Watson Assistant with a database system: https://cloud.ibm.com/docs/solution-tutorials?topic=solution-tutorials-slack-chatbot-database-watson Instead of using the Db2 interface, just use a SQL interface or database wrapper of your choice. – data_henrik Feb 21 '23 at 15:34
  • If its generating that error, then the `fetch_assoc()` did not return a row, so the query either failed or found nothing that matches the `clientid` you passed it. SIMPLES – RiggsFolly Feb 21 '23 at 15:51

0 Answers0