CODEWITHSUNDEEP
sqloracle-database
Abdus Samee
Abdus Samee

Reputation: 3

How to store a string which has multiple quotes in a database

I want to store a ciphertext in an Oracle database, but I am getting an error:

identifier is too long.

Its probably because there are multiple quotes in the string. So, how do I store such a string?

For example, my ciphertext may look like:

b't\xb2\xb2\xd6\xab\xab[\x8d\xcc\xab\x1dK\xf7\xa4\xf5\x9a\xe5\xc7\xd2\x874\xbf\xb3\xd5\xf0\xc7\xcbL\xb1\x88\xd2\xae\xeeR\xe6\xd9f\xfc\x89\xfb\xc7\xeb\x0e\xca\xbe\x88\x1e\xa8\xcb\x12\x7f\xeaL\xe5o\x01\x0c\x9f\xd1\xfc\xc2Xe\xd9H6\xa4\x02\xde\xa8\xbb\x04\xf6\xa2\x81\xe8\xa4T\x17\xe5\x94\x1a\xd1\xf3\xca\xe8\xc4v\xb2\x94\xe0,\xb8v\x9c\x13m>W6\x1cL\x87\xde\xce-h\xcd"\xa66\xac&\x9b\xc4C\x9eK\x1fL\xff\nW\x06\x06\xc1\xe3\x7f\x1c{\xff\x93\xdb\t\xdb\x13&\x81\x0c\x06\xf1\x81\x99f\n\x7f\x99\x1e\xbd\xd4\x17\xe9\x05\xb7\x97\xf6\x1f\xd5\xb3\xffK/#6A\t\xa2\xba+\xfaxO\xb9\xa7\x86\xac\x10V\xc6\xe0\x96OfF\x9f\xaaM\xe3\xc9\xf6UNO\x15\x8e\r\x00\x07J)lZ\[]N\x181\xa3\xd4\'\x8a\x91\x81\x0c\xe4:\x88\xf8\xbe\xcc\xcc\xa18\xe2.o\xe5\xb4\xd9\xd3Fk\xf9\xff\x9a\xc8\x04\xaa\x9a\xff\xc2q&\xa7\xd2O\x8eh\xd7\xa9\x02\xc5V'

As you can see there is a single and a double quote in this. So, how do I store such a string?

Upvotes: 0

Views: 396

Answers (2)

MT0
MT0

Reputation: 168361

how do I store such a string?

You appear to have binary data and not a "string"; so store it in a data type for binary data such as BLOB:

CREATE TABLE your_table (
  ciphertext BLOB
);

Then when you insert it use a parameterised query and bind variables from whatever interface you are using to access the database (from the look of your data, I would guess it is a bytes data type in Python):

For a positional bind variable, you can use the syntax:

INSERT INTO your_table ( ciphertext ) VALUES ( ? );

For a named bind variable, you can use the syntax:

INSERT INTO your_table ( ciphertext ) VALUES ( :your_value );

Then, when you construct your prepared statement to insert the value, you can pass your data in as the bind variable and you do not need to worry about any quotes.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143013

A simple option is to use the q-quoting mechanism, where you choose something (like a square bracket, curly bracket, ...) that doesn't exist in your string to enclose those values that have multiple single quotes. Otherwise you would have to escape them using double single quotes, but things get tricky once there are consecutive single quotes. It's just too complicated.

So, an example:

SQL> create table test (col varchar2(50));

Table created.

SQL> insert into test values (q'[that's a string and I'm "Little'foot"]');

1 row created.

SQL> select * From test;

COL
--------------------------------------------------
that's a string and I'm "Little'foot"

SQL>

Upvotes: 1

Related Questions