Home » SQL & PL/SQL » SQL & PL/SQL » Which is better - CHR(39) or '''' ?
icon10.gif  Which is better - CHR(39) or '''' ? [message #167465] Thu, 13 April 2006 03:53 Go to next message
sonic
Messages: 11
Registered: March 2003
Junior Member
Hi All,

Please can someone tell me which is the better option to use when the quote needs to be concatenated to a varchar2 value i.e.

In order to insert 'test' into a column exactly as yet see I used:
chr(39)||'test'||chr(39)

However, the DBA (OCP) says that is not a good way to do it and should be changed to:
''''||'test'||''''

Can someone please tell me if there's any logic to this?

Thanx
Sonic
Re: Which is better - CHR(39) or '''' ? [message #167472 is a reply to message #167465] Thu, 13 April 2006 04:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


I dont think,

chr(39)||'test'||chr(39)


is a bad way ... Even thinks its better ...

Thumbs Up
Rajuvan.
Re: Which is better - CHR(39) or '''' ? [message #167486 is a reply to message #167472] Thu, 13 April 2006 05:54 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Or:
SELECT '''test''' x
FROM   dual


MHE
Re: Which is better - CHR(39) or '''' ? [message #167762 is a reply to message #167465] Sun, 16 April 2006 22:24 Go to previous messageGo to next message
goodsadanand
Messages: 4
Registered: April 2006
Junior Member
hi,

chr() will acheive same result but has poor readability...Not everyone can make out the result on seeing chr() in the script...

If a new developer has to carry forward an exising script,
it would have poor readability with chr()
Re: Which is better - CHR(39) or '''' ? [message #167984 is a reply to message #167762] Tue, 18 April 2006 04:26 Go to previous messageGo to next message
sonic
Messages: 11
Registered: March 2003
Junior Member
Thats what the DBA said. However, its only a matter of figuring it out the first time and then you know it. Also, it gives jnr developers exposure to this function, which is quite handy e.g. what if you needed to control loops in terms of alphabets etc.

Thanx
Re: Which is better - CHR(39) or '''' ? [message #168003 is a reply to message #167984] Tue, 18 April 2006 06:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Using a feature to give ' jnr developers exposure to this function' is nonsense. If you want junior developers to know about a function, either tell them or point them to some good reference about it.
In my opinion Maarten gave the only sensible way:
'''test'''
Re: Which is better - CHR(39) or '''' ? [message #429437 is a reply to message #167472] Tue, 03 November 2009 16:04 Go to previous messageGo to next message
gsanand
Messages: 2
Registered: November 2009
Location: SF
Junior Member
I guess ''' is better than 'chr(39)'

you might not realize but each function will cause an extra CPU cycle (recursive) but it will to process your sql.
Re: Which is better - CHR(39) or '''' ? [message #429487 is a reply to message #429437] Wed, 04 November 2009 03:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I actually prefer chr(39)||'test'||chr(39)

It lets you split out the strings from the extra quotes needed to get it to display.

@Gsanand - I doubt that you could ever spot the difference in performance that this would cause.

Thinking about it, there has to be a proces in Oracle that replaces the '' with a ' inside a strnig, so it's an interesting question as to which one is (infinitesimally) quicker.
Re: Which is better - CHR(39) or '''' ? [message #430930 is a reply to message #429487] Fri, 13 November 2009 04:21 Go to previous messageGo to next message
gsanand
Messages: 2
Registered: November 2009
Location: SF
Junior Member
Well..i think it's safe to assume a function such as chr() will cause an extra recursive call. Yes ?
Re: Which is better - CHR(39) or '''' ? [message #430933 is a reply to message #167465] Fri, 13 November 2009 04:48 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
1. If 'better' is measured by readability (as seems to be the case here) then that is pretty much a subjective view and therefore the 'company policy' should be followed (it might not be an official policy, but you know what I mean)
2. I can't see there being a significant enough overhead from any of the mentioned techniques to warrant a choice based on speed.
3. if you are a junior developer and your DBA tells you that he want your code written in a specific way, i would generally suggest that you go along with that (unless the advice seems wrong in some way)
4. just to chuck the feline amongst the trafalgar square birdies i prefer a different option to those previously mentioned: using the quoted string delimiter:
insert into foo values (q'!'test3'!');

[Updated on: Fri, 13 November 2009 04:49]

Report message to a moderator

Re: Which is better - CHR(39) or '''' ? [message #430937 is a reply to message #430933] Fri, 13 November 2009 05:28 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ha! Thanks for reminding us of the quoted string delimiter. I used it years ago a few times, but have somehow completely forgotten about it.
Re: Which is better - CHR(39) or '''' ? [message #430938 is a reply to message #430937] Fri, 13 November 2009 05:34 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
I'm a fan, have been since it was introduced; particularly when building strings dynamically.
Re: Which is better - CHR(39) or '''' ? [message #430941 is a reply to message #430930] Fri, 13 November 2009 05:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Well..i think it's safe to assume a function such as chr() will cause an extra recursive call. Yes ?


You are barking up completely the wrong tree if you're trying to optimise SQL by whether or not it calls built in SQL functions. User defined functions can make a big difference to query performance. Built in functions are designed to be used this way.

The database does so much stuff behind the scenes that arguing that approach x consumes one more cpu cycle will never yield a noticable return as far as tuning goes.

I tested the time taken for 100,000,000 variable assignments (var := chr(39);, var := '''';, and var := c_chr; where c_chr is a defined constant) and the var := ''''; came out about 5% faster, which worked out as 0.4 seconds over 100 million operations.

Over 1,000,000 sql operations, the '''' approach came out at pretty much the same speed (-0.5% slower to 2% faster) - 0.05 seconds faster over 1,000,000 executions.

The potential for improvements in performance here is virtually nill. As soon as you have any other operations in the queries, these changes will disappear down into the noise.



Re: Which is better - CHR(39) or '''' ? [message #431132 is a reply to message #430941] Sun, 15 November 2009 16:25 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
CODE INJECTIONS...
Seems like no one pointed that out (or I have overlooked it) but you should avoid such inserts. Imagine following terrible situation:
CREATE TABLE usr_pwd ( usr VARCHAR2(10),pwd VARCHAR2(100));

To check if the user specified provided correct credentials unexperienced developer might use
'SELECT 1 FROM usr_pwd WHERE usr='||chr(39)||$usr||chr(39)||' AND pwd='||chr(39)||$pwd||chr(39);

Then someone might provide a username with ' and ; in it... and you would authorize the user without password.
Re: Which is better - CHR(39) or '''' ? [message #431185 is a reply to message #431132] Mon, 16 November 2009 01:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Although what you say is true, you cannot tell if this is the intended use for the question asked (unless I overlooked something in the thread).
It might be that this construct is used to generate some dynamic query string, or to concatenate a fieldvalue with a constant to create a new field value, etc, etc.
Re: Which is better - CHR(39) or '''' ? [message #431202 is a reply to message #431185] Mon, 16 November 2009 03:28 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Although I cannot tell what is the intend of the question - usually you can re-write your query to use pl/sql bound variables.
Although EXECUTE IMMEDIATE is the preferred oracle query execution syntax they allow (unnamed) bound variables there and DBMS_SQL allows named variables.
If you are using hard-coded strings in your queries then you should be aware that it is not secure.
Re: Which is better - CHR(39) or '''' ? [message #431207 is a reply to message #431202] Mon, 16 November 2009 03:47 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quote:
If you are using hard-coded strings in your queries then you should be aware that it is not secure.
Hmm not sure that that is what you actually meant to say there wakula.
Quote:
Although EXECUTE IMMEDIATE is the preferred oracle query execution syntax they allow (unnamed) bound variables there and DBMS_SQL allows named variables.
What does this have to do with the question? Just to recap, the original question was asking what was trhe best method for insrting an apostrophe into a string to be inserted into a column in a table. There was nothing about using substitution variables, nor was there anything about PL/SQL.
Re: Which is better - CHR(39) or '''' ? [message #431216 is a reply to message #431132] Mon, 16 November 2009 04:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also, you can committhe same mistake using the doubled quote syntax:
'SELECT 1 FROM usr_pwd WHERE usr='''||$usr||''' AND pwd='''||$pwd||''';'


This error is independent of how you chose to quote delimit strings in dynamic SQL.
Re: Which is better - CHR(39) or '''' ? [message #431219 is a reply to message #431202] Mon, 16 November 2009 04:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Although EXECUTE IMMEDIATE is the preferred oracle query execution syntax...

I have to take issuse with this statement.
NDS may well be the preferred (or at least simplest) way of executing dynamic SQL, but the preferred way of doing it is to avoid dynamic SQL entirely.
Re: Which is better - CHR(39) or '''' ? [message #431222 is a reply to message #431207] Mon, 16 November 2009 04:13 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
pablolee wrote on Mon, 16 November 2009 10:47
What does this have to do with the question? Just to recap, the original question was asking what was trhe best method for insrting an apostrophe into a string to be inserted into a column in a table. There was nothing about using substitution variables, nor was there anything about PL/SQL.

If you need to insert apostrophe into your SQL query then it is possibly non-secure thing. Instead you should use bound variables and then there would be probably no need to insert apostrophe.
Re: Which is better - CHR(39) or '''' ? [message #431228 is a reply to message #431222] Mon, 16 November 2009 04:23 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quote:
If you need to insert apostrophe into your SQL query then it is possibly non-secure thing. Instead you should use bound variables and then there would be probably no need to insert apostrophe.


Please show me how

INSERT INTO x VALUES (1, 'abc', chr(39)||'xyz'||chr(39));

Is insecure. there is no SQL injection issue there. The issue may arise if one is using substitution variables to supply the text strings, but that was not mentioned in any way in the initial post (nor any subsequent posts prior to your post) In addition, NDS simply would NOT be the preferred solution here if incorporating the task into PL/SQL. One would use exactly the same method and if supplying the text values at runtime, simple, local PL/SQL variables would do the job perfectly well without incurring the risk of SQL injection.
Re: Which is better - CHR(39) or '''' ? [message #431231 is a reply to message #431219] Mon, 16 November 2009 04:27 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
JRowbottom wrote on Mon, 16 November 2009 11:12

I have to take issuse with this statement.
NDS may well be the preferred (or at least simplest) way of executing dynamic SQL, but the preferred way of doing it is to avoid dynamic SQL entirely.

If I remember it correctly (sorry for not providing any links) then you are supposed to use pre-compiled SQL but sometime it is not enough. THen you need to use EXECUTE IMMEDIATE. You should avoid DBMS_SQL because it is a different style of coding than it is in general for PL/SQL. And then some things cannot be coded using only EXECUTE IMMEDIATE (or it might be easier to do them using DBMS_SQL) and this is why you have all the options still present in Oracle 11. In addition EXECUTE IMMEDIATE can be now executed using very long statements (>32k - although I have not tested that) which was available by using DBMS_SQL in Oracle 10.
Note that "some things cannot be coded using only EXECUTE IMMEDIATE"...
Re: Which is better - CHR(39) or '''' ? [message #431237 is a reply to message #431231] Mon, 16 November 2009 05:09 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Apart from being too vague to be useful, this has very little to do with the original post. Also, any chance you could answer my question?
Thanks
Re: Which is better - CHR(39) or '''' ? [message #528101 is a reply to message #431237] Fri, 21 October 2011 11:34 Go to previous messageGo to next message
dreghead
Messages: 1
Registered: October 2011
Location: Charlotte
Junior Member
If you are using 10g and above, use the quoting syntax that Oracle provides to solve this issue. q!'enter whatever you want doesn't matter!' Oracle will take whatever you put between the q!' AND AND !'. You can use other characters other than the !. You can also use [ ] or whatever you want.
Re: Which is better - CHR(39) or '''' ? [message #528115 is a reply to message #528101] Fri, 21 October 2011 13:54 Go to previous message
Michel Cadot
Messages: 63817
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow! You registered to give a solution that has already been given to a 2 years old topic?

Regards
Michel
Previous Topic: Left Function equivalent
Next Topic: How to fetch desc_tab to sys_refcursor???
Goto Forum:
  


Current Time: Fri Sep 30 15:49:07 CDT 2016

Total time taken to generate the page: 0.19211 seconds