Kirby and MariaDB?

The CMS Questions
1

Hello,

I am quite new to kirby and try to figure out some strange errors. I am using Version 4.4.1, PHP 8.2 and apache. I try to use the database implemention of kirby. That works well for updates and selects but doesn’t work for insert (yes, I use autoincrement for the id column).
I also store some files to the database (mediumblob) which may work or not, maybe depending on the file content.
Can I use kirby to do this things, can kirby work with MariaDB (mysqli) or shall I use my own implementation of database access?

2

:wave: Welcome to the forum!

Could you share more context of your current use case and implementation, so that we can see where it’s going wrong or Kirby is hitting its limits? Thanks!

3

This is how I manage the uploaded files (update):

  // authenticate as almighty
    $kirby->impersonate('kirby');
    // get the file uploads
    $uploads = [$kirby->request()->files()->get('bewerbungfile01'), $kirby->request()->files()->get('bewerbungfile02'), $kirby->request()->files()->get('bewerbungfile03')];
...
    if ($uploads[0]['tmp_name'] != NULL) {
      $data['file01'] = file_get_contents($uploads[0]['tmp_name']);
      $data['file01name'] = $uploads[0]['name'];
      $data['file01type'] = $uploads[0]['type'];
      $data['file01size'] = $uploads[0]['size'];
    }
...
    try {
      $result = Db::update('bewerbungen', array_map('escdata', $data), ['id' => $data['id']]);
      if (!$result) {
        $alerts[] = "Die Daten konnten nicht gespeichert werden.";
        return [
          'alerts' => $alerts,
          'bewerbung' => json_decode(json_encode($data), FALSE),
        ];
        exit;
      }
...

Database is InnoDB utf8mb4_general_ci

Spalte Typ Kommentar
id int(11) Auto-Inkrement
email varchar(100) NULL
name varchar(100) NULL
file01 mediumblob NULL
file01name varchar(255) NULL
file01type varchar(50) NULL
file01size int(11) NULL
file02 mediumblob NULL
file02name varchar(255) NULL
file02type varchar(50) NULL
file02size int(11) NULL
file03 mediumblob NULL
file03name varchar(255) NULL
file03type varchar(50) NULL
file03size int(11) NULL
privacy int(11) NULL
created timestamp NULL [0000-00-00 00:00:00]
updated timestamp NULL [0000-00-00 00:00:00]

Indizes

PRIMARY id

that works, but downloaded files are somehow corrupted.

To download the file I use the PHP implementation without kirby

    $conn = new mysqli($db['host'], $db['user'], $db['password'], $db['database']);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $sql = $conn->prepare(sprintf("SELECT file0%u, file0%uname, file0%utype, file0%usize FROM bewerbungen WHERE email = '%s' AND hash = '%s'", $f, $f, $f, $f, $u, $p));
    $sql->execute();
    $sql->store_result();

    if ($sql->num_rows > 0) {
        $sql->bind_result($fileData, $fileName, $fileType, $fileSize);
        $sql->fetch();
        // send Data to the browser
        header("Content-Description: File Transfer");
        header("Content-Type: " . $fileType);
        header("Content-Disposition: attachment; filename=\"" . $fileName . "\"");
        header("Expires: 0");
        header("Cache-Control: must-revalidate");
        header("Pragma: public");
        header("Content-Length: " . strlen($fileData));
        echo $fileData;
    } else {
        echo "File not found";
    }

    $sql->close();
    $conn->close();
4

Thanks, that helps a lot to actually understand your question.

I’ll ping @lukasbestle as I think he has more experience with databases than me.

Two instant thoughts:

  • Your SQL query ends with WHERE email = '%s' AND hash = '%s', but I don’t see a hash column in your database table.
  • What exactly is the escdata function doing? Could that maybe corrupt the file contents before you store them in the database? Or, do you have to unescape them after reading from the database?
5

yes there is also a hash field in the database - I shortend the table … sorry missed the hash-field.

yes … I am currently examing cssdata … that maybe the cause of the corrupt files

function escdata($v) {
  if (is_string($v)) {
    return esc($v);
  }
  return $v;
}

maybe some pdf-Files are recognized to be string …

6

Yes. After you load the uploaded file into memory with file_get_contents(), the $data['file01'] value is a binary string with the file contents.

Is there a specific reason you use esc() on all fields of your data? Please keep in mind that escaping always needs to be context-sensitive. The default context for esc() is HTML. This can be useful if you want to print the fields to an HTML page.

But it’s cleaner and safer to do the escaping on output (as close to your template output as possible), not on input into your database. This ensures that your database contains reusable content for different contexts and also reduces the risk of vulnerabilities due to output in an unexpected context.

Another recommendation from skimming through your code: Using sprintf() to build SQL queries from dynamic data is very unsafe as it allows SQL injection attacks. You can protect against this with a prepared statement. Your database engine will then take care of filling in the dynamic values in a safe way that avoids SQL injections.

7

Hello Lukas,

thank you for your answer. You are definitely right that the code with sprintf() in its current state is vulnerable to SQL injections and needs to be changed.

The error with the files was the csvdata() function. I changed the Db::update call. For what I tested, it works.

$result = Db::update('bewerbungen', array_combine(array_keys($data), array_map(fn($v, $k) => is_string($v) && !in_array($k, ['file01', 'file02', 'file03']) ? Db::escape($v) : $v, $data, array_keys($data))), ['id' => $id]);

This escapes the strings to put it to the database and excludes the binary fields.

The problem with inserts had the same origin and is solved too. Thanks a lot.

8

Glad you got it working.

BTW: You don‘t need Db::escape() either. Kirby’s higher-level database methods like Db::update() automatically take care of using prepared statements wherever possible.