CODEWITHSUNDEEP
mysql
DroidOS
DroidOS

Reputation: 8890

SQL storage inside table

I need to store a sequence of SQL statements in one of my mySQL tables for "playback" at a later stage. I sanitize, encrypt and base64 encode all user inputs. A fairly typical statement to be recorded for playback would read something along the lines of 

 INSERT INTO `skins` (sid,skid,skin) VALUES('nepi1sl3A','cici9vx4A','c') ON DUPLICATE KEY UPDATE skin = 'c';

I pass this through a filter that changes it to 

INSERT INTO `skins` (sid,skid,skin) VALUES(~nepi1sl3A~,~cici9vx4A~,~c~) ON DUPLICATE KEY UPDATE skin = ~c~;

This makes playback easier since all I have to do at that stage is a str_replace for ~.

Given the encryption + base64 encoding the ~ character can never occur in any of the VALUES.

Question - am I storing up any unforseen problems here. If so is there a safer/better way of accomplishing the same thing?

After having read some of the comments * answers I felt a bit more clarification is due

Initially I was encrypting + base64 encoding the actual statement being stored for playback. However, on reflection I felt that the need to base64 decode + decrypt prior to playback was imposing an unnecessary overhead since the original statement (with all user data sanitized, encrypted & base64 encoded) was not high risk anyway. If I want to replace the quotes prior to storage it is to provide one final layer of security just in the,unlikely, event that one day I forget to do the sanitization/encryption/base64 encoding steps on user data.

Upvotes: 1

Views: 29

Answers (1)

Barmar
Barmar

Reputation: 781340

I don't see any fatal problems with what you're doing. As far as the database is concerned, this is just opaque data -- the fact that it represents SQL queries is irrelevant. If there's any danger, it would be based on where the SQL queries come from (e.g. user input); saving them in a table doesn't make it more or less dangerous.

As I mentioned in the comments, you don't need to replace the quotes in the statement. Quotes only cause a problem if you try to substitute the statement with quotes into a query. But since you're encoding the statement before substituting it into the query, the quotes will be gone.

Ideally you should be using an API with parametrized queries rather than substituting directly into INSERT statements. E.g. if you use PHP, you should use PDO or MySQLi.

Upvotes: 1

Related Questions