How Does One Escape an Apostrophe in Db2 SQL

How does one escape an apostrophe in db2 sql

Use two apostrophes '' to get a single apostrophe on DB2 too, according to the DB2 Survival Guide. Isn't that working for you?

How to escape apostrophe in a db2 sql query, running within a shell script?

You should escape the single quotes as with a backlash as in :

su - myid -c 'db2 connect to mydb;db2 -x -v "select COL1,\'\',\'\',COL2,\'\',\'\',COL3L from MYTABLE fetch first 10 rows only"; db2 connect reset;'

Beware, I didn't test it... no shell at hand just now.

UPDATE:

Finally I got my hands on a DB2 instance.. after a little testing i got it working.
It turns out that the previous syntax was faulty. The proper way of quoting the single quote is (in this case) '\'' as in:

su - myid -c 'db2 connect to mydb;db2 -x -v "select COL1,'\'','\'',COL2,'\'','\'',COL3L  from MYTABLE fetch first 10 rows only"; db2 connect reset;'

That's because the single quote around the whole command must be closed (') in order to supply the escape for the single quote in the db2 query (\') and then reopened to resume the command quoting ('). Weird as it looks, it works....

This is the command I used to test it:

bash -c 'db2 connect to mydb;db2 -x -v "select 1,'\'','\'',2,'\'','\'',3  from SYSIBM.SYSDUMMY1 fetch first 10 rows only"; db2 connect reset;'

How to insert a value with single apostrophe in DB2

The escape character for single quote is double single quote. For example, to insert St.Mary's you need to do

 INSERT INTO TABLE VALUES ('St.Mary''s')

However, it is for plain SQL. It depends how you call it from Java. If the column is a parameter marker in a prepared statement, you do not need that.

Also, if you pass the parameter from other components, probably you need to escape the single quote character.

How can I inserting a string with a single quote in it into a DB2 table?

try this (double quote)

Insert into EMP values ('Raju''s kumar' , 10000)

db2_escape_string replacement function for db2 on i

I would suggest using prepared statements (db2_prepare, followed by db2_execute) instead of raw sql (db2_exec) as a way of avoiding the string escape problem. The parameters you pass into db2_execute will be automatically escaped in the correct way.

If you are running into encoding issues, utf8_decode might help.

Prepared statements prevent the possibility of hostile SQL injection. In DB2, they also let you insert more than 32k of data at a time.

How to escape single quotes for SQL insert...when string to insert is in a user generated variable

You can do either of the below:

1. Use the PreparedStatement class. (Recommended)

   String userString="a'bcd";
   String myStatement = " INSERT INTO MYTABLE (INSERTCOLUMN) VALUES (?)";
   PreparedStatement statement= con.prepareStatement   (myStatement );
   statement.setString(1,userString);
   statement.executeUpdate();
   

2. Escape the single quotes.

   In SQL, single quotes will be escaped by using double single quotes. ' --> ''

   String userString="a'bcd";
   String changedUserString = userString.replace("'","''");
           //changedUserString  = a''bcd
   String insertTableSQL = "INSERT INTO myTable (insertColumn) VALUES("
                           +" '"+changedUserString +"' )";
   

single quotes escape during string insertion into a database

try

string sql= "insert into gtable (1text, 1memo) " + 
            "values ('" + textBox3.Text.Replace("'", "''") + "', null)";

---

Related Topics