Self Modifying Code, I’m Guessing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Self Modifying Code, I’m Guessing

Author

Topic

Bill Z
Starting Member

27 Posts

Posted - 2014-11-11 : 09:44:01

Got a hard one for me and I don’t know where to start, so I can use some valid suggestions.

I want to know who is authorized to see each GL number.

The application is proprietary code purchased from a vendor and they will not give away secrets. But I can read the SQL database and create whatever queries I wish.

The database (Microsoft SQL 2008) keeps what looks like an executable SQL statement but I don’t know how to execute it within SQL. Sort of self modifying code, I’m guessing. No matter what I try, I can’t seem to execute the information in the field to see who is authorized. There must be alternate ways of doing this. More explanation to follow:

In my general ledger database the 13 alpha-numeric character general ledger number is made up of several segments (Orgn0, Orgn1, Orgn2, Orgn3 & Orgn4). If it makes any difference, Orgn0 is 4 characters, Orgn1 is 2 characters, Orgn2 is 3 characters, Orgn3 is 2 characters and Orgn4 is 2 characters.

The authorization to view any general ledger number is stored in 1 field in the database table and here are 3 examples of User_view table

User View
User_Bob (orgn4 in ('30','57','58','63')) and (orgn3 <> '00')
User_Sue (orgn4 in ('00','18','54','57','58','64','69','98')) and (orgn3 <> '00')
User_Jake (orgn4 in ('00','91','FX')) or ((orgn0 between '1993' and '6993') and (orgn4 between 'JD' and 'JG'))) and (orgn3 <> '00')

GL_Num table would look like this

key_orgn orgn0 orgn1 orgn2 orgn3 orgn4 account description
1995230489918 1995 23 048 99 18 6411.TO NULL
1995110481118 1995 11 111 11 18 6399.00 NULL
1995111101130 1995 11 111 11 30 6299.CO NULL
1995111101130 1995 11 111 11 30 6299.00 NULL
1995111171137 1995 11 111 11 37 6321.00 TRANSFER
1995111171137 1995 11 111 11 37 6399.00 TRANSFER
1995319999964 1995 31 199 99 64 6339.00 NULL
1995319999964 1995 31 199 99 64 6299.00 NULL

So, you see, it looks like they should be able to be executed in SQL. But how?

Could I use SQL to build a C++, Java or Python program that uses this script as code, then call SQL again from the C++, Java or Python program to check each user to validate if they can use a certain GL.

Where do I start?

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-11 : 09:54:23

you would use dynamic sql. build a query as an nvarchar string adding the View from the User_view table to the end of the WHERE clause. Then execute the sql using sp_executesql function

Bill Z
Starting Member

27 Posts

Posted - 2014-11-17 : 10:12:25

Have not ever tried the EXECUTE statement. Thanks for suggesting it.
This weekend I put myself to the task of learning all about the EXECUTE statement.

I am having some difficulty understanding how SQL knows the relationship between my view_text field in the User_view table and the declaration @ParmDefinition. Is it because it is the only field in the select statement?

I used some examples from the Internet and I can get SQL to execute with no errors but it does not give me anything in return. So, in my learning process, I have missed something.

Just so you know and understand to better help me, the table apprdetl is a table of folks that are in the work flow to approve. The user_view table has the parameters in the field view_txt that tells what specific GL numbers they can approve.

I'm trying to get a list of GL numbers for each person in the apprdetl table.


DECLARE @SQLGLNum     nvarchar(MAX);
DECLARE @SQLView      nvarchar(MAX);
DECLARE @ParmDefinition nvarchar(max);


SET @SQLGLNum = N'SELECT key_orgn, orgn0, orgn1, orgn2, orgn3, orgn4 
from dbo.GL_Num
where 
';

set @SQLView =
N'select view_txt 
from dbo.User_view
where not left (@ParmDefinition,4) = '' 1=1'' 
and viewcode like ''ORGN%''
and (user_id in (select rapprover
				from dbo.apprdetl
				where rrequired = ''Y''
				and lvl in (''4'',''5''))
	or user_id in (select ralternate
				from dbo.apprdetl
				where rrequired = ''Y''
				and lvl in (''4'',''5''))	
	or user_id in (select ralternate2
				from dbo.apprdetl
				where rrequired = ''Y''
				and lvl in (''4'',''5''))
	)
order by user_id
';

declare @sql nvarchar(max) =
	@SQLGLNum + @ParmDefinition;

EXECUTE sp_executesql @sql;

GL_Num table would look like this

key_orgn	orgn0	orgn1	orgn2	orgn3	orgn4	account	description
1995230489918 1995	23	048	99	18	6411.TO NULL
1995110481118 1995	11	111	11	18	6399.00 NULL
1995111101130 1995	11	111	11	30	6299.CO NULL
1995111101130 1995	11	111	11	30	6299.00 NULL
1995111171137 1995	11	111	11	37	6321.00 TRANSFER 
1995111171137 1995	11	111	11	37	6399.00 TRANSFER 
1995319999964 1995	31	199	99	64	6339.00 NULL
1995319999964 1995	31	199	99	64	6299.00 NULL




User_view table

User		View
User_Bob	(orgn4 in ('30','57','58','63')) and (orgn3 <> '00') 
User_Sue	(orgn4 in ('00','18','54','57','58','64','69','98')) and (orgn3 <> '00') 
User_Jake	(orgn4 in ('00','91','FX')) or ((orgn0 between '1993' and '6993') and (orgn4 between 'JD' and 'JG'))) and (orgn3 <> '00') 




apprdetl table

App_group  lvl	rapprover	ralternate	ralternate2	prequired
IT Group    4	user_Bob					Y
Sec Group   4	User_Sue	User_Jake			Y

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-17 : 10:58:01

One thing that jumps out is this line:


where not left (@ParmDefinition,4) = '' 1=1''

The problem here is that you are building the variable ref into the string to be executed. However, the EXECUTE command runs in a different context and does not have access to your variables. So you need something like this:


where not left(' + @ParmDefinition + ',4) = '' 1=1''

so that the variable is resolved when the string is being built. The alternate (safer actually) method is to use the system procedure sp_executesql. That allows you to pass the parameters to the stored procedure execution.

Bill Z
Starting Member

27 Posts

Posted - 2014-11-17 : 14:05:52

If I were to print out the @sql before I executed it, I would want it to end up something like this where the parameters after the 'AND' statement changes for each user_view.

SET @SQLGLNum = N'SELECT key_orgn, lvl, fund, orgn1, orgn2, orgn3, orgn4
from dbo.bubudorgn
where yr = ''15''
and (orgn4 in ('30','57','58','63')) and (orgn3 <> '00')

Where the parm --> orgn4 in ('30','57','58','63')) and (orgn3 <> '00') changes with each new @SQLView. This way I can go through my database and know what GL numbers each user can approve.

At least that I what I need to do.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-17 : 14:37:33

That's pretty easy to do.

[code]
declare @orgn4 nvarchar(max);
set @orgn4 = N'orgn4 in ('30','57','58','63')'
SET @SQLGLNum = N'SELECT key_orgn, lvl, fund, orgn1, orgn2, orgn3, orgn4
from dbo.bubudorgn
where yr = ''15''
and ' + @orgn4

Bill Z
Starting Member

27 Posts

Posted - 2014-11-17 : 15:58:54

I am soo sorry that I am not able to explain that the view is actually a field in the user_view table and I want to, somehow, use the field in the SQL command.

You (gbritton) suggested to use EXECUTE but I still can not figure how to set the information in the field 'View' of the User_view table as a SQL qualifying statement. I don't want to physically read the information then type in another SQL for each of the 160 qualified to approve. I want sql to do the work. For lack of a better term, the contents of @ParmDefinition will change many times as the users are identified in SQL.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-17 : 16:16:31

Can you provide an example of one hard-coded query that works as you desire. We can work backwards from that to make it dynamic.

Bill Z
Starting Member

27 Posts

Posted - 2014-11-17 : 16:46:25

These work and produce the following results (Results are only a few records for obvious reasons.


select view_txt
from dbo.sectb_usrviw
where viewcode like 'ORGN%'
and (user_id in (select rapprover
				from dbo.apprdetl
				where rrequired = 'Y'
				and lvl in ('4','5'))
	or user_id in (select ralternate
				from dbo.apprdetl
				where rrequired = 'Y'
				and lvl in ('4','5'))	
	or user_id in (select ralternate2
				from dbo.apprdetl
				where rrequired = 'Y'
				and lvl in ('4','5')))



SELECT key_orgn, lvl, orgn0, orgn1, orgn2, orgn3, orgn4 
from dbo.bubudorgn
where (((orgn0 between '1993' and '6993') and (orgn4 between '51' and '55') and lvl = '6') or ((orgn0 between '1993' and '6993') and (orgn4 in ('13','57','58','63')) and lvl = '6')) and (orgn3 <> '00' and lvl = '6')

-- The above where statement is what is in the view_txt field in the sectb_usrviw table





view_txt

(((orgn0 between '1993' and '6993') and (orgn4 between '51' and '55') and lvl = '6') or ((orgn0 between '1993' and '6993') and (orgn4 in ('13','57','58','63')) and lvl = '6')) and (orgn3 <> '00' and lvl = '6') 
(((orgn0 between '1993' and '6993') and (orgn4 between '51' and '55') and lvl = '6') or ((orgn0 between '1993' and '6993') and (orgn4 in ('17','57','58','63','69','TR')) and lvl = '6')) and (orgn3 <> '00' and lvl = '6')                         
(((orgn0 between '1993' and '6993') and (orgn4 between '51' and '55') and lvl = '6') or ((orgn0 between '1993' and '6993') and (orgn4 in ('00','08','09','16','57','58','60','61','63','66','69')) and lvl = '6'))                                                                                                                                          


key_orgn	lvl	orgn0	orgn1		orgn2		orgn3		orgn4
1993110471157   6	1995    11            	047            	11             	57              
1993111072458   6	1995    11             	107            	24             	58              
1993111191157   6	1995    11             	119            	11             	57              
1993111202458   6	1995    11             	120            	24             	58              
1993130012254   6	1995    13             	001            	22             	54              
1993130433169   6	1995    13             	043            	31             	69              
1993360999957   6	1995    36             	099            	99             	57              
1993369992254   6	1995    36             	999            	22             	54              
2113110443098   6	2115    11             	044            	30             	98           





(76 row(s) affected)

(3533 row(s) affected)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-17 : 22:19:29

I think I get it. The WHERE Clause is pulled from dbo.sectb_usrviw. Is that correct? And you want to issue the second query against dbo.bubudorgn using the where clause pulled from the first table. Is that correct?

Bill Z
Starting Member

27 Posts

Posted - 2014-11-18 : 10:39:07

Yes!!

Is EXECUTE the way? The only way?

The application for this database uses something to do this. That is why this field is in the database.

I'm just such a new user to SQL that I don't know where to start.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 10:50:37

OK -- here's a framework


DECLARE @SQL NVARCHAR(MAX) = '
...basic query before variable substitution..
WHERE '
DECLARE @WHERE NVARCHAR(MAX) = (
    SELECT ... query to extract the where clause
)

EXECUTE (@SQL + @WHERE)

Bill Z
Starting Member

27 Posts

Posted - 2014-11-18 : 12:03:40

Your suggestion does build a SQL but all I get is an error when trying to execute.

DECLARE @SQLGLNum nvarchar(MAX) = 'SELECT key_orgn, lvl, fund, orgn1, orgn2, orgn3, orgn4 
from dbo.bubudorgn
where ';


DECLARE @Where nvarchar(MAX) = '(select view_txt 
		from dbo.sectb_usrviw
		where viewcode like ''ORGN%''
		and (user_id in (select rapprover
				from dbo.apprdetl
				where rrequired = ''Y''
				and lvl in (''4'',''5''))
	or user_id in (select ralternate
				from dbo.apprdetl
				where rrequired = ''Y''
				and lvl in (''4'',''5''))	
	or user_id in (select ralternate2
				from dbo.apprdetl
				where rrequired = ''Y''
				and lvl in (''4'',''5''))
	)
)';

print  (@SQLGLNum + @Where)

execute  (@SQLGLNum + @Where)

Result is:

SELECT key_orgn, lvl, fund, orgn1, orgn2, orgn3, orgn4
from dbo.bubudorgn
where (select view_txt
from dbo.sectb_usrviw
where viewcode like 'ORGN%'
and (user_id in (select rapprover
from dbo.apprdetl
where rrequired = 'Y'
and lvl in ('4','5'))
or user_id in (select ralternate
from dbo.apprdetl
where rrequired = 'Y'
and lvl in ('4','5'))
or user_id in (select ralternate2
from dbo.apprdetl
where rrequired = 'Y'
and lvl in ('4','5'))
)
)
Msg 4145, Level 15, State 1, Line 19
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 12:20:04

Your main WHERE clause begins with '(select view_txt..' and ends at the last ')' but that is not a valid WHERE clause. That is I cannot say:


select 1 where (select 2)

though I could say


select 1 where exists (select 2)

Not that that's what you want. IIUC (BIG if!) you want something like this:


DECLARE @Where nvarchar(MAX) = (select top(1) view_txt 
		from dbo.sectb_usrviw
		where viewcode like 'ORGN%'
		and (user_id in (select rapprover
				from dbo.apprdetl
				where rrequired = 'Y'
				and lvl in ('4','5'))
	or user_id in (select ralternate
				from dbo.apprdetl
				where rrequired = 'Y'
				and lvl in ('4','5'))	
	or user_id in (select ralternate2
				from dbo.apprdetl
				where rrequired = 'Y'
				and lvl in ('4','5'))
	)

The point is to extract view_txt (which contains the where condition, right?) and set the variable @where to that text. Note that I added the TOP(1) in case that query can possibly return more than one row. Otherwise it will fail if more than one row is returned. If that is even remotely a possibility, use TOP (1) and add a meaningful ORDER BY clause.

Edited to remove double quotes

Bill Z
Starting Member

27 Posts

Posted - 2014-11-18 : 13:14:49

To answer the above question; Yes, I expect there to be 76 or more different view_txt values that will give many GL numbers. If there was only one, I could key it in and be done with it.

I've been looking at the view_txt field and can see that it is defined to hold as many as 3500 characters. The most I have right now is 388. Saying this because I was thinking that the error I'm getting 'Incorrect syntax near ')'.' just maybe stuff past the last parenthesis in the view_txt field. If this is true, the other problem is that most all of the records are of different lengths. I thought about substituting but what with what. However, I'm probability wrong here.

Here is what I have coded and the result.

DECLARE @SQLGLNum nvarchar(MAX) = 'SELECT key_orgn, lvl, fund, orgn1, orgn2, orgn3, orgn4 
from dbo.bubudorgn
where ';


DECLARE @Where nvarchar(MAX) = '(select top(2) left(char( view_txt ),400)
		from dbo.sectb_usrviw
		where viewcode like ''ORGN%''
		and (user_id in (select rapprover
				from dbo.apprdetl
				where rrequired = ''Y''
				and lvl in (''4'',''5''))
	or user_id in (select ralternate
				from dbo.apprdetl
				where rrequired = ''Y''
				and lvl in (''4'',''5''))	
	or user_id in (select ralternate2
				from dbo.apprdetl
				where rrequired = ''Y''
				and lvl in (''4'',''5''))
	)'



print  (@SQLGLNum + @Where)

execute  (@SQLGLNum + @Where)

Results:

SELECT key_orgn, lvl, fund, orgn1, orgn2, orgn3, orgn4
from dbo.bubudorgn
where (select top(2) left(char( view_txt ),400)
from dbo.sectb_usrviw
where viewcode like 'ORGN%'
and (user_id in (select rapprover
from dbo.apprdetl
where rrequired = 'Y'
and lvl in ('4','5'))
or user_id in (select ralternate
from dbo.apprdetl
where rrequired = 'Y'
and lvl in ('4','5'))
or user_id in (select ralternate2
from dbo.apprdetl
where rrequired = 'Y'
and lvl in ('4','5'))
)
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near ')'.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 13:23:16

Reread my last post. Notice that I did NOT set @WHERE to a SQL query as a string. I set @WHERE to the RESULT of that query.

Bill Z
Starting Member

27 Posts

Posted - 2014-11-18 : 13:51:43

Seems my 2008 server sees that there are multiple returns for the query and doesn't like it.

The code as I submitted it. I have been shortening the code to reduce confusion of commas and parenthesis.


DECLARE @SQLGLNum nvarchar(MAX) = 'SELECT key_orgn, lvl, fund, orgn1, orgn2, orgn3, orgn4 
from dbo.bubudorgn
where ';

DECLARE @Where nvarchar(MAX) = (select view_txt
		from dbo.sectb_usrviw
		where viewcode like 'ORGN%'
		and not left(view_txt,4) = ' 1=1' 
		and user_id in (select rapprover
				from dbo.apprdetl
				where lvl = '4')
								)

print  (@SQLGLNum + @Where)

execute  (@SQLGLNum + @Where)

Result:

Msg 512, Level 16, State 1, Line 28
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'where'.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 14:02:38

Look back at my previous post. I warned that the query might return more than one value and that you needed to handle that. The easiest way is to use SELECT TOP(1) and specify an appropriate ORDER BY.

Also, you have an unclosed parenthesis. You need a right parenthesis just before the "print" command.

Bill Z
Starting Member

27 Posts

Posted - 2014-11-18 : 14:17:02

OK!

This is all so new to me.

So, by putting top(1) in the select of the declared value, it won't just get the very first all of the time but when use like this it actually get the next one? Is this correct?

I poo poo this before because I didn't understand. Yes, it does work.


DECLARE @SQLGLNum nvarchar(MAX) = 'SELECT key_orgn, lvl, fund, orgn1, orgn2, orgn3, orgn4 
from dbo.bubudorgn
where ';

DECLARE @Where nvarchar(MAX) = (select top (1) view_txt
		from dbo.sectb_usrviw
		where viewcode like 'ORGN%'
		and not left(view_txt,4) = ' 1=1' 
		and user_id in (select rapprover
				from dbo.apprdetl
				where lvl = '4')
								)

print  (@SQLGLNum + @Where)

execute  (@SQLGLNum + @Where)

Results:

key_orgn lvl orgn0 orgn1 orgn2 orgn3 orgn4
1993110192552 6 1993 11 019 25 52
1993110441151 6 1993 11 044 11 51
1993110452355 6 1993 11 045 23 55
1993110471157 6 1993 11 047 11 57
1993110482552 6 1993 11 048 25 52
1993111041163 6 1993 11 104 11 63
1993111072458 6 1993 11 107 24 58
1993111092153 6 1993 11 109 21 53
1993111111152 6 1993 11 111 11 52
1993111122552 6 1993 11 112 25 52

Thanks a bunch. I can use this. I have learned much from this experience.
_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 14:26:35

quote:
So, by putting top(1) in the select of the declared value, it won't just get the very first all of the time but when use like this it actually get the next one? Is this correct?

Caution! If you use TOP(1) without ORDER BY, SQL will not necessarily return the same row every time. Remember that SQL is set-based, and there is no order to members of a set. Depending on the size of the table, the indexing, the number of CPUs, the data in the cache and numerous other factors, SQL "may" return the same row every time or a different one each time. That is why I recommend adding an ORDER BY clause to be sure that you get exactly what you need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources