Hi there

Running on SA 16.0.0.1915 using jConn4:

The Error caused by this simple thing (code is coldfusion, #...# will be replaced by it before SA sees it):

begin
   declare @now timestamp;
   set @now = current timestamp;
   update USERS
      set SESSION_KEY = hash (string(USER_GUID, '#cgi.REMOTE_ADDR#', @now), 'SHA1'),
          SESSION_KEY_IP = '#cgi.REMOTE_ADDR#',
          SESSION_KEY_ISSUED = @now,
          SESSION_KEY_TOUCHED = null
    where IS_SYSTEM_ = 0
      and USER_HASH = :uh ;

   select SESSION_KEY 
     from dba.USERS as u

       -- for next line to work it must not contain :uh again although it should!
       -- embedding the value literally does work but is not intended of course
    where USER_HASH = '#arguments.User_Hash#'  
      for read only;
end;

Note that this works where I have only ONE parameter with the same name. Set the commented WHERE clause to the same expression as above i.e. using the same parameter again, SA chokes.

Very annoying because embedding parameters literally bypasses parameter checks for all the web-related cheating like sql injection etc. etc. luckily again, having it at least for one parameter working, still prevents the script.

Hints to fix that are very welcome.

Tinu

asked 29 May '14, 08:32

Tinu8805's gravatar image

Tinu8805
41226
accept rate: 0%


Read this doc page: http://dcx.sybase.com/index.html#sa160/en/dbusage/ptib.html*d5e5480

Host variable references are permitted within batches with the following restrictions:

  • only one statement in the batch can refer to host variables

  • the statement which uses host variables cannot be preceded by a statement which returns a result set

permanent link

answered 02 Jun '14, 11:47

JBSchueler's gravatar image

JBSchueler
2.1k2837
accept rate: 15%

There are massive behavior changes in this area from SA12x to SA16 ... I have productive applications out there which do lots of batch stuff in SA12 ...

I have to rewrite almost all of them to work in SA16 ... and this stuff is not mentioned in chapters "Behavior changes in SA16" in the docs AFAIK.

(09 Jun '14, 08:00) Tinu8805
Replies hidden

According to SA12 documentation, there is no change with regard to host variable restrictions. See http://dcx.sybase.com/index.html#1201/en/dbusage/ptib.html.

(09 Jun '14, 11:45) JBSchueler

Is the statement wrapped between cfquery tags?

If yes, use

<cfqueryparam value="#arguments.User_Hash#" cfsqltype="cf_sql_varchar">

and not embedded sql host variables like USER_HASH = :uh

permanent link

answered 29 May '14, 09:34

Vincent%20Buck's gravatar image

Vincent Buck
70681520
accept rate: 15%

edited 29 May '14, 09:35

your comment does not apply here. the whole statement is

q = new query(); the_sql = "..... all the above ...."; q.setSQL (the_sql); q.addParam (name="uh", value=arguments.User_Hash, cfsqltype="CF_SQL_VARCHAR"); res = q.execute().getResult().SESSION_KEY;

i have done such things a million times .. I assume a simple programming bug: some intermediate piece of software (SA, jConn) calculates: find parameter names and occurrences in sql, compare count with count of input parameters, if not equal ... and voila, it breaks

(29 May '14, 09:52) Tinu8805

@tinu8805 So you're using cfscript... Did you do a dump of the generated SQL and run it in ISQL? Also, I'm not sure you can run a compound statement of the sort in CF. You could break it down in 2 queries between transaction functions. Or use a stored proc.

(29 May '14, 10:40) Vincent Buck
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×86

question asked: 29 May '14, 08:32

question was seen: 2,614 times

last updated: 10 Jun '14, 03:18