Converting MYSQL UUID unicode characters from json response to hex in php

Question

I get an API response with a mysql error in the browser.

INSERT INTO category_certificate (category_id, certificate_id, created_at) VALUES (\u0027\ufffdl\ufffd=\ufffd\ufffdA\u01e0\\\f\ufffd\ufffd\ufffd\ufffd\ufffd\u0027,\u0027\u0010\ufffd\u0019\ufffd\u0370A\ufffd\ufffd\ufffd\ufffdV\u001d\ufffd\u0027,\u00272022-07-04 14:57:24.766\u0027);\u0027:\n\nSQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (shopware.category_certificate, CONSTRAINT FK_9C7A6A4799223FFD FOREIGN KEY (certificate_id) REFERENCES category_certificate (id) ON DELETE CASCADE)

I want to find out the hex of the UUIDs used in this SQL query. Tried few things like json_decode, or mb_convert_encoding the \u0027\ufffdl\ufffd=\ufffd\ufffdA\u01e0\\\f\ufffd\ufffd\ufffd\ufffd\ufffd\u0027 string like mentioned here https://stackoverflow.com/a/6058533/7275814 but I always just get the unicode chars not the human readable hex'd UUID. How can I convert this, to process with debugging?

Answer 1

As @Michas already mentioned in a comment it is not possible to regain the original binary data from that string. If you're in a dev environment, then you should be able to look at the response to your API request and find a URL to the Symfony Profiler page for that request under the header X-Debug-Token-Link. On the Profiler page you might be able to find the Query under the Doctrine tab with readable parameters if you look at the runnable version. However they might not be readable there, as the UUIDs will probably have been converted to binary data already at that point. Then your best bet would be the Exception tab where you might be able to find a call in the stack that exposes the original payload, e.g. a call to ApiController::executeWriteOperation.