Db exception trying to upload SSL certificate

[marabesi]

ISSUE TYPE

• Bug Report

COMPONENT NAME

Accounts > SSL certificates

CLOUDSTACK VERSION

4.12.0.0-SNAPSHOT (pulled from docker hub)

CONFIGURATION

N/A

OS / ENVIRONMENT

SUMMARY

I am developing a feature that integrates with the SSL certificate endpoint, but I couldn't make ir work by the UI interface nor by the API end point.

#2558 might be a related issue.

The following bug occurs in the latest version pulled from docker (4.12.0.0-SNAPSHOT) and 4.9.*.

STEPS TO REPRODUCE

Via UI:

1) Login with admin user
2) Go to the accounts menu
3) Select the tab "SSL Certificates"
4) Click in "Add Certificate"
5) Fill in the field "Name"
6) Fill in the field "Certificate"
7) Fill in the field "Private Key"

Via PHP code (pseudo code using https://github.com/PCextreme/cloudstack-php):

// Not that I am sending the certificate and the 
// private key content instead of the absolute path to the file

$client->uploadSslCert( [
    'certificate' => file_get_contents('my.cert')),
    'privatekey' => file_get_contents('cstest.key'),
    'password' => 'test',
]);

EXPECTED RESULTS

A certificate should be stored and listed in the table

ACTUAL RESULTS

A SQL exception is thrown trying to upload a SSL certificate.

Response from API: 
````
DB Exception on: com.mysql.jdbc.JDBC4PreparedStatement@7a02a22a: INSERT INTO sslcerts (sslcerts.id, sslcerts.uuid, sslcerts.certificate, sslcerts.chain, sslcerts.key, sslcerts.password, sslcerts.account_id, sslcerts.domain_id, sslcerts.fingerprint) VALUES (null, _binary'eb111f4e-56df-4190-8702-c70e6d899d8c', _binary'-----BEGIN CERTIFICATE-----\nMIIDsTCCApmgAwIBAgIJANQsgM2iQR1JMA0GCSqGSIb3DQEBCwUAMG8xCzAJBgNV\nBAYTAkVTMQ8wDQYDVQQIDAZNYWRyaWQxDzANBgNVBAcMBk1hZHJpZDEcMBoGA1UE\nCgwTRGVmYXVsdCBDb21wYW55IEx0ZDEgMB4GA1UEAwwXbWFyYWJlc2kuam90ZWx1\nbHUuc3BhY2UwHhcNMTgxMjE4MDkwNDE1WhcNMTkxMjE4MDkwNDE1WjBvMQswCQYD\nVQQGEwJFUzEPMA0GA1UECAwGTWFkcmlkMQ8wDQYDVQQHDAZNYWRyaWQxHDAaBgNV\nBAoME0RlZmF1bHQgQ29tcGFueSBMdGQxIDAeBgNVBAMMF21hcmFiZXNpLmpvdGVs\ndWx1LnNwYWNlMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA4YpX1i+5\nv6vPHUQa3YN63hn+LxONyGjdx1XllC+rpPZM4C7/A3ywsU5QmyPBkTCYIqpeP8vT\nZL/lcv6+RV0PqXnUUzrZpSGOBL0boLPcyYgJErANMQJ8fO4dru9WsUXW12QjKXP7\notbxgrJqSDw1fUYIUTXMuckncAVPwTv82cSnLWO5HI3P02XkEHsZeDeVaF/0F4xA\nJZNjf+gOyBJxYYd3pRx82qUtcPz8tkRFkAgxzuM/JIz+ZMKkiJb4wJASteo43hbh\nL4pcszLiOqDFaLNJYOpLeG/R9xHWNORc212bYvxjaeNXeaRs1WW1MMxG4LVqv9us\nVuyAogwz/9S8BQIDAQABo1AwTjAdBgNVHQ4EFgQUbZPozHgoQRDqQExbMRKFqv6G\nSBcwHwYDVR0jBBgwFoAUbZPozHgoQRDqQExbMRKFqv6GSBcwDAYDVR0TBAUwAwEB\n/zANBgkqhkiG9w0BAQsFAAOCAQEAPNJPPRfnBNJih9rtI3p/fI0fQ60AcDMQLAwA\nRT+cAfpO2xO93aM1rhkBJ6+touTU6c0pYfr9sy10g6t6it8sRral+aYYuXlzbykV\nkFwyHAVetJUZnPc0Dljs4nQFyoe38FjGVNcY/+5A34DWmlg2YXurr05XXetpvkZ2\n4ElfPAtRA4DseLgjLhNWvn/4Q54PLKrFtFZU+u2+y8/u/Xk3jUVPzzXTs0HXta3U\nl1iTbK/JPvUN/C41MMmwGEV5G0TD7ulEbtoNysqdpRuOx3GCCbeyRqIbZXjWZ0Et\nSuEVuCt+0CkwMPCwnsr/SYXAJm/gd5RoS0DdyiRsh4dG6SvlUQ==\n-----END CERTIFICATE-----', null, _binary'ZrAwtQ+eJ6s1So2Wuwl0Yde9nBXsePuFKHNuXxp2w9H1yY2uFKQpmXdK9Donre76kUshK+294sEEo6oOzlwI0Eo7q9dKYN3p3nJNL0CnaGV8iH72YKdMxRlH0uLWdnc4gKdpO1bN67zv4w2srmO9RC7t7PScDiTj4ph0O2TBSAX9eGmY6m9HApNUcuGBrcZjNKRnp+UuOvqyUeBbyp4D+E5Anq6Aqw3nA/PuMQhBHotP+HZQO00KqwzvyqGwf9zbR7+Ci+xncXTipTo30Mnyu+FUUiMbObe5mONWO/t58qq7SJhTNTljsVhC/qGxHub0uua4URto7SiclXtoKW7yKpHKGrXqQINVTU0goBCa/KtdCWK6CG1ctOO249Z52mWi4C6CX23q7837+prIoMX5xLawuk2vIElZzYxDCiWaxFab8OfFh+Ir6J2UBW9A4YcdTw9PEUIbK01A1hKXXMeNvpToEnLuc6XW+Cw/3XTTVS4uESVgBFNgAoil5erqF/bayQ2UnLHxyIa/at072iqv764Y3Vsn6qKMrSi9Z8VFg20P3GhLWuG3/NKqDehOKw97WD1y6EGwy047hpguRV5orCaM1cNMtZZPOBu592F2H9v9LfJrchuLoiTuVUcfkkpreuJYErwcQtXLnjIRiUTRdIvgDsd/u8MnhEORItUR7TEvZemzbRxNRtR22ybYQt7vMW6bcK+j9jKYEqM85xak+oEtx1fIa6gxSR6MDgflIGFZEilaz3Ey0srpPfmkxGNkGT8CmM6ymEsKOs7/Yo4apG/uxIE9A8dFdOT1f6aP9fb4QRlyNqOc9plWjTWDkVW7MlcRbsCGPzvs9Al36ZFQg5W2TkEXfLYPLvZhCMo/lStnwe2ArgqeYtNXEFgzSR+IRjJlA1u1WtS7CU3azbcQD0Flq5Cak4WvUkEktaQzKPY+EbdIoAGfCDAukeDWgD/vbzsPTfygbVLAFCJfSOca2DELWcYALPbJtKEydDcCXaIZ4c1YzULTW8OiiCfFtdnU9A5wwXlYpKUXNvYK/mCRIGb9Vatnedm6yl9f5UkAXPtMDCS9LcPqeBofJ5AEAsQsvpreRd2YNRGw8mXtdBhGNuoVgu5sbtfvrQW/U9gXhZNIrltVfRcwdu2/YMY547mc7w3oZjvwDGXxCynuyg52pPebn9qHABN6iQo+6gTYVyOHF3MXmCMeMnHfaxYS6NZFR4er9yrAid6jNDTpVxVCs9Osxic/atWDG7lavOI+eCXe/DflLmrWGQM/12VSKanren/yD6CqwsYVE2kGzXE9sDbXdLtZH+dfghI1Bv/rZODcj7zfBj4TjHJQO2N6B2i4D2VLoK3xvMOBE3cMeFHxT64W3Hi7+sBdO7ohXIZmD8kGH6Kdct06yVf53gIIE9FaprnzYEKqdz7SM05xVFkbOjOX8cr4zz32WwRAvIp6k/jrnX7JwdwNO/gz6HijSOjRf0oADF8oX9/0ulVPKsm3D8XZxrVabT6YUiHfgKcwuof3Oet0yk3++qMwvZb3dsSh2bfPiZ/pgpqxXCMal9J8o/VABwr4ZPkvkvwbVAJKMkWFpAwc83YYINmrA+WFSkzex5XYYVMFAcn6C3FQ7Hno2z28QtTAkwQhpZdiE+qyZ3ArZRAGtPOjVawYKYNlP9/sFNk42/c65CXYHpEOeJXLQiwxVD9l7S6qz/6hPAuL3CPyWbYm8bKOdKdUg77JAuX5A1rzBpK4HNz4TgdTBEjhHQqJJec/yJHV/i6EeYOhNqSgK62XaTzGgmfnAj9xPuqTleoFJVEE30QH+7zj92iLXQGiMZzmX/1qZPbkBs3nIPXhE5/yEFItoDvHd0fEII6gyOlob3fqT7FYvXSYcvsWny+jXjctTJJvoesoeUHX+SI0KBOfFCfBogiZTcgZafTkPAAKcrpEK54yj0Fc4IuXJmLAGVFQY8iCzpa9E3fKyE0KTljD2ZyqInUmZ/+oHZmozxaGT479XhRk/qUWLRQdRF/jTdG8QvJ/xiwASIe9mza9jyj9NLH03qHrCxWUeplBtoF5gf74lYqaQJT23814RflSlgskLe6aYM+cXdNpqkeLFXMb2ensnm++RWatd0+q+IpaIgl+4Uzpa7MV84Ujm/9uQlK4Uq5qQG7ZX18kPhw+GsgIqm021nnu4S8JE1wXNnwaEJ/RAevVkqyymvcUlXFMtXxpeI56x9j6h3pfPDMH8F+GYUOJnk+NJdQWUyOXW9RA15M6iTHC0jysZW1fKgqqg/bm0jKKFN91UlKuU45hJquOvYZy0w==', null, 1056, 1050, _binary'56:D6:2E:0F:BC:F7:93:B7:BA:4F:29:6D:5C:23:43:D8:34:C8:69:6B:9D:62:63:2C:59:D1:3B:6F:43:46:FA:6A')
````

Response from UI:

[yadvr]

Can you try to run the sql using mysql client, likely one of the columns (varchar) needs resizing.

[marabesi]

Hello @rhtyd you are correct, the column fingerprint was varchar(62) and the data being stored was bigger.

Executing the same SQL directly in the database I got the following:

SO I just changed the fingerprint type to TEXT type, with the following:

alter table sslcerts modify fingerprint text;

Once I execute the command, the UI allowed me to upload the certificate:

[marabesi]

The question now is, should the column be changed in the official cloudstack repo?

[marabesi]

@rhtyd the file engine/schema/src/main/resources/META-INF/db/schema-421to430.sql has the SQL to create the sslcerts table:

CREATE TABLE `sslcerts` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `uuid` varchar(40) DEFAULT NULL,
      `account_id` bigint(20) unsigned NOT NULL,
      `domain_id` bigint(20) unsigned NOT NULL,
      `certificate` text NOT NULL,
      `fingerprint` varchar(62) NOT NULL,
      `key` text NOT NULL,
      `chain` text,
      `password` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      CONSTRAINT `fk_sslcert__account_id` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE,
      CONSTRAINT `fk_sslcert__domain_id` FOREIGN KEY (`domain_id`) REFERENCES `domain` (`id`) ON DELETE CASCADE
);

Is it the place where it supposed to change?

[rafaelweingartner]

If you want to target master, it should go in 41120-41200 SQL file