The Dangers of Dynamic SQL
October 5, 2005 Ted Holt
The term “dynamic SQL” refers to SQL commands that are interpreted at runtime. The SQL command is loaded into a statement string (host variable), then the PREPARE statement tells the SQL processor to compile the statement string into a prepared statement (an executable command). Here’s a short example of the process.
D SqlCommand s 1024a C eval SqlCommand = C 'select * from qcustcdt + C where state =''' + C %trim(CtlState) + '''' C/exec sql C+ prepare SqlStmt from :SqlCommand C/end-exec C/exec sql C+ declare Input cursor for SqlStmt C/end-exec
Dynamic SQL provides great flexibility, since the statement string can contain any SQL command and can be loaded by any means. However, this flexibility can lead to security problems, so you have to be careful how you use dynamic SQL. Let’s look at some examples of how the programmer’s intentions can be circumvented.
Let me say first that most security problems with dynamic SQL occur when PCs are communicating with a database server, especially through ODBC connections and CGI applications. Those of us who have spent our careers processing fixed-length fields and variables may sometimes forget that on other platforms things are not so neat and clean. Whereas we can expect a two-byte state code from a display file, a PC-based application may send a variable-length string that can be much longer than we expect. However, I find it easiest to illustrate how a user can sabotage an SQL command if I use the green screen, so I’m using that medium for my illustrations.
The examples all prompt for a state abbreviation, then load and display a subfile of customers from that state. Since a string coming from another system can be quite long, I’ve allowed 64 bytes–much more than two bytes for the state entry field–in my display file.
* display file AAA200D A DSPSIZ(24 80 *DS3) A REF(QCUSTCDT) A CF03(03) CF12(03) A R SFL SFL A SFLSEQ 3S 0O 7 2 A CUSNUM R O +1 A INIT R O +1 A LSTNAM R O +1 A STREET R O +1 A CITY R O +1 A STATE R O +1 A ZIPCOD R O +1 A CHGCOD R O +1 A CDTLMT R O +1 A BALDUE R O +1 A CDTDUE R O +1 A R CTL SFLCTL(SFL) A 41 SFLDSP A 42 SFLDSPCTL A N41N42 SFLCLR A 43 SFLEND A SFLSIZ(0016) A SFLPAG(0015) A 1 33'Customer Inquiry' A DSPATR(HI) A 3 4'State:' A CTLSTATE 64 B 3 11 A 6 4' '
The QCUSTCDT file around which this small application is designed is in library QIWS. Now let’s look at the first example RPG program that loads and displays the subfile.
* program AAA200R H option(*srcstmt: *nodebugio) Faaa200d cf e workstn sfile(sfl:sflseq) D CustRec e ds extname(QCUSTCDT) D SqlCommand s 1024a C/exec sql C+ set option closqlcsr=*endmod C/end-exec C/exec sql C+ declare Input cursor for SqlStmt C/end-exec C exsr Inz C dou *in03 C exsr LoadSfl C exsr DisplaySfl C enddo C return C* ======================================================= C LoadSFL begsr C C exsr PrepareCursor C exsr ClearSfl C/exec sql C+ open Input C/end-exec C dow '1' C/exec sql C+ fetch Input into :CustRec C/end-exec C if SqlStt >= '02000' C leave C endif C eval SflSeq += 1 C write Sfl C enddo C/exec sql C+ close Input C/end-exec C C endsr C* ======================================================= C DisplaySfl begsr C C eval *in42 = *on C eval *in41 = (SflSeq > *zero) C eval *in43 = *on C exfmt Ctl C C endsr C* ======================================================= C ClearSfl begsr C C eval *in41 = *off C eval *in42 = *off C write Ctl C eval SflSeq = *zero C C endsr C* ======================================================= C PrepareCursor begsr C C eval SqlCommand = C 'select * from qcustcdt + C where state =''' + C %trim(CtlState) + '''' C/exec sql C+ prepare SqlStmt from :SqlCommand C/end-exec C C endsr C* ======================================================= C Inz begsr C C eval *inlr = *on C C endsr
Notice the PREPARE statement, close to the end of the source. If the user enters a two-character state abbreviation preceded and/or followed by blanks into field CTLSTATE, everything is copacetic. The SQL command looks like this:
select * from qcustcdt where state ='TX'
The user sees just the data for the selected state, which is Texas in this example. But what if the user enters additional characters, like this?
TX' OR '1'='1
The generated SQL command looks like this:
select * from qcustcdt where state ='TX' OR '1'='1'
Now the SQL retrieves everything in the database, regardless of state, because ‘1’=’1′ is true for every record.
Let’s alter the example a bit. In the next version of the RPG program, the SQL command contains a compound condition to prevent the user from seeing any customer with a credit limit of $1,000 or more.
C eval SqlCommand = C 'select * from qcustcdt + C where state =''' + C %trim(CtlState) + C ''' and CDTLMT < 1000'
If the user enters a two-byte state code, the system selects customers from that state with a credit limit less than $1,000.
select * from qcustcdt where state ='TX' and CDTLMT < 1000
But the user can easily turn off the second condition by sending this string to the program.
TX' --
The double hyphen tells the SQL processor to treat the remainder of the command as comments.
select * from qcustcdt where state ='TX' --' and CDTLMT < 1000
Therefore, the system displays all records from the selected state.
|
One good way to prevent the user from doctoring the SQL command is to use parameter markers. Parameters are indicated by question marks in the statement string. Here’s another version of the first program using a parameter marker for the state code.
* program AAA201R H option(*srcstmt: *nodebugio) Faaa200d cf e workstn sfile(sfl:sflseq) D CustRec e ds extname(QCUSTCDT) D SqlCommand s 1024a D StateCode s 2a C/exec sql C+ set option closqlcsr=*endmod C/end-exec C/exec sql C+ declare Input cursor for SqlStmt C/end-exec C exsr Inz C exsr PrepareCursor C dou *in03 C exsr LoadSfl C exsr DisplaySfl C enddo C return C* ======================================================= C LoadSFL begsr C C exsr ClearSfl C eval StateCode = %trim(CtlState) C if StateCode = *blanks C leavesr C endif C/exec sql C+ open Input using :StateCode C/end-exec C dow '1' C/exec sql C+ fetch Input into :CustRec C/end-exec C if SqlStt >= '02000' C leave C endif C eval SflSeq += 1 C write Sfl C enddo C/exec sql C+ close Input C/end-exec C C endsr C* ======================================================= C DisplaySfl begsr C C eval *in42 = *on C eval *in41 = (SflSeq > *zero) C eval *in43 = *on C exfmt Ctl C C endsr C* ======================================================= C ClearSfl begsr C C eval *in41 = *off C eval *in42 = *off C write Ctl C eval SflSeq = *zero C C endsr C* ======================================================= C PrepareCursor begsr C C eval SqlCommand = C 'select * from qcustcdt + C where state = ?' C/exec sql C+ prepare SqlStmt from :SqlCommand C/end-exec C C endsr C* ======================================================= C Inz begsr C C eval *inlr = *on C C endsr
Besides the question mark in the statement string, notice the USING clause in the OPEN command. When the cursor is opened, the state code is assigned to the SQL command, retrieving only the records from the desired state. It will not do the user any good to send extra characters after the state abbreviation.
It is amazing how much information hackers can glean about the system by doctoring a parameter in the address entry field of a Web browser. Rather than filling in the usual entry fields, the hacker doctors the parameter value on the end of a URL in the address field. Hackers can find the names of tables, fields within tables, and contents of tables, even those containing user IDs and passwords. Hackers also execute other SQL statements, such as CREATE TABLE, UPDATE, DELETE, and INSERT, which allows them to modify the database and give themselves access to the system.
To learn more about how people doctor SQL commands, use your favorite search engine to look for SQL injection.
