Reducing Trips to the Server
Developer changes can affect how a user interacts with a page. Slow performance and screen flicker, which occurs whenever the screen refreshes after a server trip, are significant issues for users.
This chapter discusses how to:
Reduce server trips.
Use better coding techniques for improved performance.
Write more efficient code.
Search PeopleCode for SQL injection.
Reducing Trips to the Server
This section discusses how to:
Count server trips.
Use deferred mode.
Hide and disable fields.
Use the Refresh button.
Update totals and balances.
Use warning messages.
Use the fastest algorithm.
Server trips are bad for performance. Each server trip consumes resources on the application server, slows down the user data entry, and can affect type ahead. Whenever you see an hourglass as you move between fields on a page, it is because the browser is waiting for a server trip to complete.
The larger the component’s buffer (based on the number of record definitions accessed, the number of fields in each record, and the number of rows in each grid or scroll area for each record), the longer each round trip to the server, because of the increased server processing.
Deferred mode reduces the user’s time to complete the transaction and conserves application server resources.
The following user interactions cause a trip to the server. Only the first three items in the list are deferred in deferred processing mode.
Entering data in fields with FieldEdit or FieldChange PeopleCode.
Entering data in fields that have prompt table edits.
Entering data in fields that have related displays.
Inserting a row in a grid or scroll area.
Deleting a row from a grid or scroll area.
Using grid or scroll area controls to move forward or back.
Accessing another page in the component.
Selecting an internal tab.
Expanding or collapsing a collapsible section.
Clicking a button or link.
Each trip goes through the same process of checking security, unpacking the buffers that store the data being processed, processing the service request, generating the HTML for the page to be redisplayed, packing updated buffers, and storing the buffers on the web server. To maximize online performance, minimize server trips.
Counting Server Trips
Count the trips to the server to quickly identify transactions that have performance issues. PeopleTools can automatically count these trips by reason (such as, adding a row in a grid or FieldChange PeopleCode) and write the output to a log file.
To turn this feature on, run a debug version of PeopleTools and add the following to the [trace] section of the appserv.cfg file:
showcounters = 1
The output is written to the appsrv.log file.
Using Deferred Mode
Keep components in deferred mode and enable fields for interactive mode only if there is a strong business case.
For every field on the component to run in deferred mode, Deferred mode must be selected at the component level, Allow Deferred Processing must be selected for each page in the component, and Allow Deferred Processing must be selected for each field.
PeopleSoft recommends that you continue to code field edits in FieldEdit PeopleCode and field change logic in FieldChange PeopleCode, but set this logic to run in deferred mode. You do not need to move field edits to SaveEdit.
Hiding and Disabling Fields
Avoid using FieldChange PeopleCode to hide, unhide, enable, or disable elements on the same page, unless the element is triggered by a separate button.
Hiding or unhiding objects and enabling or disabling objects should, as a general rule, be coded in either page Activate PeopleCode or, for objects that are on another page in the component, in FieldChange PeopleCode.
Perform cross-validation edits to prevent invalid data combinations from being written to the database for fields that previously would have been hidden or unavailable. If unhiding fields that were previously hidden or unavailable results in making the page confusing, consider designing a longer page so that users can easily associate related fields.
You can hide or unhide objects or set them to display-only in page Activate PeopleCode before the page initially appears based on setup data, configuration options, or personalization settings. You can set fields to display-only using PeopleCode by setting the DisplayOnly property for the field to True.
You can hide or unhide fields on another page, or set the fields to display-only, based on the value that a user enters in a field on the current page, as long as that component or field is set up to run in deferred processing mode. In some cases, it may make sense to split transactions across pages to achieve progressive disclosure.
Using the Refresh Button
The Refresh button gives users control of their environment. Clicking the Refresh button forces a trip to the server. PeopleTools then redisplays the page in the browser. The refresh action allows the user to:
See related display field values for the data entered so far.
See any default values based on data entered previously on the page.
Validate the data that has been entered on the page so far.
When the page is redisplayed, the cursor is positioned in the same field it was when the user pressed the Refresh button.
Note. The Refresh button does
not refresh the page from the database. It simply causes a server trip so
that any deferred PeopleCode changes are processed. If no deferred changes
exist or the deferred changes do not cause any errors or other changes on
the page, it may appear to the user as if nothing has happened.
Fields on derived work records are not updated if the user clicks the Refresh button.
Updating Totals and Balances
In some pages, totals or balances appear based on data entered into a grid or scroll area. This process should work in deferred mode also, showing the totals or balances as of the last trip to the application server.
Continue to keep any accumulation and balancing logic in FieldChange PeopleCode, but run the field in deferred mode. Users can click the Refresh button at any time to see the latest totals based on the data entered. Totals and balances in deferred mode are always updated and displayed after any trip to the application server.
Using Warning Messages
In deferred mode, FieldEdit PeopleCode errors and warnings do not appear when a user moves out of the field, but rather on the next trip to the server. This next trip might not occur until the user enters all the data and clicks the Save button.
For FieldEdit error messages running in deferred mode, PeopleTools changes the field to red and positions the cursor to the field in error when it displays the message. This behavior allows the user to associate the error message with a specific field.
For warning messages, however, PeopleTools does not change the field to red or position the cursor. For a user to clearly understand to which field a warning message applies, ensure that warning messages clearly describe the fields affected by the warning.
For example, the warning message “Date out of range” would be confusing if there are seven date fields on the page, since a user could not easily determine which date field needed to be reviewed. Instead, you could include bind variables in the message to show which dates are out of range.
Using the Fastest Algorithm
You should determine which algorithms perform the best and have the smallest elapsed time. Tracing does not provide subsecond level of timing information. Plus, tracing imposes a higher overhead to the runtime environment, which skews the elapsed time reading.
However, you can use the %PerfTime system variable for determining elapsed time. %PerfTime retrieves the local system clock time by making a system call, and the return time is down to the millisecond.
The following example of %PerfTime determines how long a program takes to execute:
&Start = %PerfTime; &results = ""; For &I = 1 To &Count; &GnnwgNumber = GetNextNumberWithGapsCommit(QEORDER_DTL.QE_QTY, 999999, 1,⇒ "where QE_ORDER_NBR='GNNWG'"); &results = &results | " : " | &GnnwgNumber; End-For; &End = %PerfTime; &out = "Count = " | &Count | ", total GNNWG time (s) = " | NumberToString⇒ ("%6.3", Value(&End - &Start));
Using Better Coding Techniques for Improved Performance
This section discusses how to:
Run a SQL trace.
Optimize SQL.
Use the GetNextNumberWithGaps function.
Consolidate PeopleCode programs.
Move PeopleCode to a component or page definition.
Send messages in the SavePostChange event.
Use metadata and the RowsetCache class.
Setting MaxCacheMemory
Running a SQL Trace
Run a SQLTrace and review the transaction for SQL statements that have a long processing time.
The duration column (Dur= ) in a SQL trace displays this information. If the duration is greater than 100 milliseconds, you may be able to make this SQL statement run faster. Work with your database administrator to tune the SQL.
Optimizing SQL
A simple join optimizes SQL more effectively than issuing two related SQL statements separately.
However, if your transaction requires a complex SQL statement (for instance, one that uses correlated subqueries), consider breaking it up into multiple SQL statements. You may get more predictable performance this way.
Using the GetNextNumberWithGaps Function
Many applications use a sequence number as a unique key. The last number used is stored in a common table, and a SQL statement is issued to retrieve the last number used and update the table. This action locks the common table until the whole transaction is saved and the unit of work committed.
Instead, consider using the GetNextNumberWithGaps PeopleCode function whenever gaps in the sequence numbering are acceptable. The function retrieves the last number used, increments it by one, and updates the common table. This action is done in a separate unit of work to minimize the time a database lock is held on the common table.
GetNextNumberWithGaps issues a commit only when issued from the SavePreChange or Workflow event.
Consolidating PeopleCode Programs
Consolidate RowInit PeopleCode into one field within the record to reduce the number of PeopleCode events that need to be triggered. Fewer PeopleCode programs results in fewer PeopleCode objects to manage. Do the same for RowInsert, SaveEdit, SavePreChange, SavePostChange, and Workflow PeopleCode programs.
Moving PeopleCode to a Component or Page Definition
Analyze transactions and move PeopleCode that is specific to a component from the record definition to the component or page definition. This action eliminates the need to execute conditional statements, such as If %Component = .
This action helps only if you are able to move all the PeopleCode in a program from the record to a component or page, and multiple components access that record.
Sending Messages in the SavePostChange Event
Messages sent online should always be coded in the SavePostChange event. To minimize the time that PeopleTools maintains locks on single-threaded messaging tables, behind-the-scenes logic in the SavePostChange event defers sending the message until just before the commit for the transaction.
Using Metadata and the RowsetCache Class
If your application uses data that is common, used by a number of users, and yet is fairly static, you may see a performance improvement by using the RowsetCache class.
PeopleTools stores application data in a database cache to increase system performance. The RowsetCache class enables you to access this memory structure, created at runtime, and shared by all users.
Note. Non-base language users may see different performance due to language table considerations.
See RowsetCache Class.
Setting MaxCacheMemory
PeopleTools stores application data in a memory cache to increase system performance. However, too large a cache can leave insufficient available memory on your system, which leads to reduced performance.
Use this setting to specify the maximum size of the memory cache. PeopleTools prunes the cache to keep it within the specified size, and places the pruned data in a disk cache instead. Because using a disk cache can also reduce performance, the default setting might not be optimal for your application. You can adjust this setting to achieve the best trade-off between speed and available memory.
See Cache Settings.
Writing More Efficient Code
Follow these steps to write more efficient PeopleCode:
One of the conveniences of PeopleCode is that you do not have to declare your variables before you use them. The variable is assigned a type of ANY, taking on the type of the value it is assigned. However, if you use this feature, you lose type-checking at compile time, which can lead to problems at runtime.
When you validate or save PeopleCode, watch for auto-declared messages and consider adding declarations to your program.
Declare variable types specifically.
Most of the time, you know a variable's type, so you should declare the variable of that type when you begin.
For example, if you know that a particular variable is going to be an Integer value, declare it to be Integer in the first place. You can get much better runtime performance. It is particularly effective for loop control variables but, since an integer has limited range (up to 9 or 10 digits), you must use it judiciously.
In PeopleCode function calls, parameters are passed by reference; a reference to the value is passed instead of the value itself. If you are passing a reference to a complex data structure, such as a rowset object or an array, passing by reference saves significant processing.
Watch out for unexpected results, though. In the following code, the function Test changes the value of &Str after the function call.
Function Test(&Par as String) &Par = "Surprise"; end-function; Local String &Str = "Hello"; Test(&Str); /* now &Str has the value "surprise" */
Put Break statements in your Evaluate statements.
In an Evaluate statement, the When clauses continue to be evaluated until an End-evaluate or a Break statement is encountered.
If you have an Evaluate statement with a number of When clauses, and you only expect one of them to match, put a Break statement following the likely clause. Otherwise, all the subsequent When clauses are evaluated. Your program is still correct, but it is inefficient at runtime, particularly if you have a large number of When clauses, and the Evaluate statement is in a loop.
One of the key features in PeopleSoft Pure Internet Architecture is that the application server is stateless. When required, the state of your session is bundled up and exchanged between the application server and the web server.
For example, on a user interaction, the whole state, including your PeopleCode state, has to be serialized to the web server. Then, once the interaction has completed, that state is deserialized in the application server so that your application can continue.
To improve efficiency:
Watch the size of PeopleCode objects that you create (strings, arrays, and so on) to make sure they are only as big as you need them to be.
For user interactions, you might be able to change the logic of your program to minimize the state.
For example if you are building up a large string (a couple of megabytes) and then performing a user interaction, you might be able to change your program logic to build the string after the interaction.
For secondary pages that are infrequently accessed but retrieve lots of data, consider setting No Auto Select in the Application Designer for the grids and scroll areas on the secondary page, to prevent loading the data the secondary page when the page buffers are initially built.
Then add the necessary Select method to the Activate event for the secondary page to load the data into the grid or scroll area.
The PeopleCode compiler is not an optimizing compiler, unlike some current compilers for languages such as C++. For example, the PeopleCode compiler does not do common subexpression analysis. So, sometimes, if you have a complicated bit of PeopleCode that is used often, you can isolate the common expression yourself. This isolation can make your code look cleaner and make your code faster, especially if it is in a loop.
In this example, notice how the common subexpression is broken out:
/*---- For this customer, setup time on B is influenced by *---- the machine flavors of A. */ &r_machine = &rs(&idB.GetRecord(Record.MACHINE_INFO); If (&typeA = "F") And (&typeB == "U") Then &r_machine.SETUP_TIME.Value = 50; Else &r_machine.SETUP_TIME.Value = 10; End-If;
The compiler has to evaluate each occurrence of the expression, even though it would only execute it once.
Here is another example. Notice that once &RS and &StartDate are created, they can be used repeatedly in the loop, saving significant processing time.
&RS = GetRowset(); &StartDate = GetField(PSU_CRS_SESSN.START_DATE).Value; For &I = 1 To &RS.ActiveRowCount &RecStuEnroll = &RS.GetRow(&I).PSU_STU_ENROLL; &Course = &RecStuEnroll.COURSE; &Status = &RecStuEnroll.ENROLL_STATUS; &PreReqStart = &RS.GetRow(&I).PSU_CRS_SESSN.START_DATE.Value; If &Course.Value = "1002" And (&Status.Value = "ENR" Or &Status.Value = "CMP") Then If &PreReqStart < &StartDate Then &Completed = True; Break; End-If; End-If; End-For;
The most common implicit conversion is from a character string to a number and vice versa. You might not be able to do anything about this, but—by being aware of it—you might be able to spot opportunities to improve performance.
In the following example, two character strings are converted into numeric values before the difference is taken. If this code were in a loop and one of the values did not change, performance would improve significantly by doing the conversion once, as the second statement illustrates.
&Diff = &R1.QE_EMPLID.Value - &R2.QE_EMPID.Value; &Original = &R1.QE_EMPLID.Value; . . . &Diff = &Original - &R2.QE_EMPID.Value;
In certain cases, use SQLExec, as it only returns a single row. In other cases, you could benefit greatly by using a SQL object instead, especially if you can plan to execute a statement more than once with different bind parameters. The performance gain comes from compiling the statement once and executing it many times.
For instance, code that uses SQLExec might look like this:
While (some condition) . . .set up &Rec SQLExec("%Insert(:1)", &rec); /* this does a separate tools parse of the sql and db compile of the statement and execute each time */ End-while;
The following code rewrites the previous example to use the new SQL object:
Local SQL &SQL = CreateSQL("%Insert(:1)"); While (some condition) . . .Setup &Rec &Sql.Execute(&Rec); /* saves the tools parse and db compile on the SQL statement and the db setup for the statement */ end-while;
SQL objects also have the ReuseCursor property, which can be used for further performance gains.
See ReuseCursor.
Examine loops to see if code can be placed outside the loop.
For example, if you are working with file objects and your file layout does not change, there is no reason to set the file layout every time you go through the loop reading lines from the file. Set the file layout once, outside the loop.
Set objects to NULL when they will no longer be accessed.
Once you are finished with an object reference, especially one with a global or component scope, assign it to NULL to get rid of the object. This setting allows the runtime environment to clean up unused objects, reducing the size of your PeopleCode state.
Improve your application classes
Simple properties (without get/set) are much more efficient than method calls. Be clear in your design about what needs to be simple properties, properties with get/set, and methods. Never make something a method that really should be a property.
Analyze your use of properties implemented with get/set. While PeopleCode properties are in a sense first class properties with more flexibility in that you can run PeopleCode to actually get and set their values, make sure you actually need get and set methods. If all you have is a normal property which is more of an instance variable then avoid get/set methods. In the following example (without the strikethrough!) by having get/set for the property SomeString you have made it much more inefficient to get/set that property since every property reference has to run some PeopleCode. Often, this inneficiency can creep in when properties are designed to be flexible at the beginning and never subsequently analyzed for whether getters/setters were really needed after all.
class Test ... property String SomeString
get set; end-class; get SomeString return &SomeString; end-get; set SomeString &SomeString = &NewValue; end-set;
Writing More Efficient Code ExamplesThese examples demonstrate more efficiently written code:
Beware of the rowset Fill method. (Or, "What not to do in a Application Engine PeopleCode step.")
Sometimes you need to examine the algorithm you are using. The following example is a PeopleCode program that adopts this approach: read all the data into a rowset, process it row by row, and then update as necessary. One of the reasons this is a bad approach is because you lose the general advantage of set-based programming that you get with Application Engine programs.
Local Rowset &RS; Local Record &REC; Local SQL &SQL_UPDATE; &REC_NAME1 = "Record." | SOME_AET.SOME_TMP; &RS = CreateRowset(@(&REC_NAME1)); &LINE_NO = 1; &NUM_ROWS = &RS.Fill("WHERE PROCESS_INSTANCE = :1 AND BUSINESS_UNIT = :2 AND⇒ TRANSACTION_GROUP = :3 AND ADJUST_TYPE = :4 ", SOME_AET.PROCESS_INSTANCE, SOME_⇒ AET.BUSINESS_UNIT, SOME_AET.TRANSACTION_GROUP, SOME_AET.ADJUST_TYPE); For &I = 1 To &NUM_ROWS &REC = &RS(&I).GetRecord(@(&REC_NAME1)); &REC.SOME_FIELD.Value = &LINE_NO; &REC.Update(); &LINE_NO = &LINE_NO + 2; End-For;
This code has the following problems:
You might run out of memory in the Fill method if the Select gathers a large amount of data.
The Fill is selecting all the columns in the table when all that is being updated is one column.
You can change this code to read in the data one row at a time using a SQL object or using a similar algorithm, but chunking the rowsets into a manageable size through the use of an appropriate Where clause.
The following are some approximate numbers you can use to see how large a rowset can grow. The overhead for a field buffer (independent of any field data) is approximately 88 bytes. The overhead for a record buffer is approximately 44 bytes. The overhead for a row is approximately 26 bytes. So a rowset with just one record (row) the general approximate formula is as follows:
memory_amount = nrows * (row overhead + nrecords * ( rec overhead + nfields * ( field overhead) + average cumulative fielddata for all fields))
The following are some code examples to show isolating common expressions.
In this example, a simple evaluation goes from happening three times to just once– &RS_Level2(&I).PSU_TASK_EFFORT. In addition, the rewritten code is easier to read.
Example of code before being rewritten:
Local Rowset &RS_Level2; Local Boolean &TrueOrFalse = (PSU_TASK_RSRC.COMPLETED_FLAG.Value = "N"); For &I = 1 To &RS_Level2.ActiveRowCount &RS_Level2(&I).PSU_TASK_EFFORT.EFFORT_DT.Enabled = &TrueOrFalse; &RS_Level2(&I).PSU_TASK_EFFORT.EFFORT_AMT.Enabled = &TrueOrFalse; &RS_Level2(&I).PSU_TASK_EFFORT.CHARGE_BACK.Enabled = &TrueOrFalse; End-For;
Example of code after being rewritten:
Local Boolean &TrueOrFalse = (PSU_TASK_RSRC.COMPLETED_FLAG.Value = "N"); For &I = 1 To &RS_Level2.ActiveRowCount Local Record &TaskEffort = &RS_Level2(&I).PSU_TASK_EFFORT; &TaskEffort.EFFORT_DT.Enabled = &TrueOrFalse; &TaskEffort.EFFORT_AMT.Enabled = &TrueOrFalse; &TaskEffort.CHARGE_BACK.Enabled = &TrueOrFalse; End-For;
In the next example, the following improvements are made to the code:
Shorthand is used: &ThisRs(&J) instead of &ThisRs.GetRow(&J).
Eliminated all the autodeclared messages by declaring all the local variables. This action can improve your logic and possibly give you better performance.
Notice the integer declaration. If you know your variables will fit in an integer (or a float), then declare them that way. Runtime performance for Integers can be better than for variables declared as Number.
Fewer evaluation expressions.
Example of code before being rewritten:
Local Row &CurrentRow; &TrueOrFalse = (GetField().Value = "N"); &CurrentRow = GetRow(); For &I = 1 To &CurrentRow.ChildCount For &J = 1 To &CurrentRow.GetRowset(&I).ActiveRowCount For &K = 1 To &CurrentRow.GetRowset(&I).GetRow(&J).RecordCount For &L = 1 To &CurrentRow.GetRowset(&I).GetRow(&J).GetRecord(&K).FieldCount &CurrentRow.GetRowset(&I).GetRow(&J).GetRecord(&K).GetField(&L).Enabled⇒ = &TrueOrFalse; End-For; End-For; End-For; End-For;
Example of code after being rewritten:
Local Row &CurrentRow; Local integer &I, &J, &K, &L; Local boolean &TrueOrFalse = (GetField().Value = "N"); &CurrentRow = GetRow(); For &i = 1 To &CurrentRow.ChildCount /* No specific RowSet, Record, or Field is mentioned! */ Local Rowset &ThisRs = &CurrentRow.GetRowset(&i); For &J = 1 To &ThisRs.ActiveRowCount Local Row &ThisRow = &ThisRs(&J); For &K = 1 To &ThisRow.RecordCount Local Record &ThisRec = &ThisRow.GetRecord(&K); For &L = 1 To &ThisRec.FieldCount &ThisRec.GetField(&L).Enabled = &TrueOrFalse; End-For; End-For; End-For; End-For;
Concatenating a large number of strings into a large string. Sometimes you need to do this.
The simplest approach is to do something like:
&NewString = &NewString | &NewPiece;
In itself this is not a bad approach but you can do this much more efficiently using an application class below.
class StringBuffer method StringBuffer(&InitialValue As string); method Append(&New As string) returns StringBuffer; // allows &X.Append⇒ ("this").Append("that").Append("and this") method Reset(); property string Value get set; property integer Length readonly; property integer MaxLength; private instance array of string &Pieces; end-class; method StringBuffer /+ &InitialValue as String, +/ &Pieces = CreateArray(&InitialValue); &MaxLength = 2147483647; // default maximum size &Length = Len(&InitialValue); end-method; method Reset &Pieces.Len = 0; &Length = 0; end-method; method Append /+ &New as String +/ Local integer &TempLength = &Length + Len(&New); If &Length > &MaxLength Then throw CreateException(0, 0, "Maximum size of StringBuffer exceeded(" | &Max⇒ Length | ")"); End-If; &Length = &TempLength; &Pieces.Push(&New); return %This; end-method; get Value /+ Returns String +/ Local string &Temp = &Pieces.Join("", "", "", &Length); /* collapse array now */ &Pieces.Len = 1; &Pieces[1] = &Temp; /* start out with this combo string */ Return &Temp; end-get; set Value /+ &NewValue as String +/ /* Ditch our current value */ &Pieces.Len = 1; &Pieces[1] = &NewValue; /* start out with this string */ &Length = Len(&NewValue); end-set;
Use this code as follows:
Local StringBuffer &S = create StringBuffer(""); .... &S.Append(&line); /* to get the value of string simply use &S.Value */
Searching PeopleCode for SQL Injection
SQL injection is a technique that enables users to pass unintended SQL to an application. SQL injection is usually caused by developers who use string-building techniques to generate SQL that is subsequently executed.
PeopleSoft recommends you search your PeopleCode for SQL injection vulnerabilities.
To search for potential SQL injection vulnerabilities:
Open Application Designer.
Select Edit, Find In. .
From the Find In dialog box, select SQL Injection in PeopleCode as the find type.
Only potential vulnerabilities will be found.
Review flagged PeopleCode programs.
Vulnerable PeopleCode programs allow unvalidated user input concatenated to SQL.
The following functions and methods provide a way for SQL to be submitted to the database; they are, therefore, subject to SQL injection vulnerabilities:
SQLExec function
CreateSQL function
Rowset class Select method
Rowset class SelectNew method
Rowset class Fill method
Rowset class FillAppend method
Look at the following PeopleCode as an example:
rem Retrieve user input from the name field; &UserInput = GetField(Field.NAME).Value; SQLExec("SELECT NAME, PHONE FROM PS_INFO WHERE NAME='" | &UserInput | "'", &Name, &Phone);
The code is meant to enable the user to type in a name and get the person's phone number. In the example, the developer expects that the user will input data such as Smith, in which case the resulting SQL would look like this:
SELECT NAME, PHONE FROM PS_INFO WHERE NAME='Smith'
However, if the user specified "Smith' OR AGE > 55 --", the resulting SQL would look like this:
SELECT NAME, PHONE FROM PS_INFO WHERE NAME='Smith' OR AGE > 55 --'
Note the use of the comment operator (--) to ignore the trailing single quotation mark placed by the developer's code. This would allow a devious user to find everyone older than 55.
Preventing SQL Injection
Use the following approaches to avoid SQL injection vulnerabilities:
Where possible, avoid using string-building techniques to generate SQL.
Note. String-building techniques cannot always be avoided. String-building does not pose a threat unless unvalidated user input is concatenated to SQL.
Use bind variables where possible rather that string concatenation.
The following example is vulnerable:
SQLExec("SELECT NAME, PHONE FROM PS_INFO WHERE NAME='" | &UserInput | "'", &Name, &Phone);
Use the Quote PeopleCode function on the user input before concatenating it to SQL.
This pairs the quotation marks in the user input, effectively negating any SQL injection attack.
The following example is vulnerable:
SQLExec("SELECT NAME, PHONE FROM PS_INFO WHERE NAME='" | &UserInput | "'", &Name, &Phone);
This example is not vulnerable:
SQLExec("SELECT NAME, PHONE FROM PS_INFO WHERE NAME='" | Quote(&UserInput) | "'", &Name, &Phone);
Specify whether SQL errors appear to the user with the Suppress SQL Error setting in the PSTOOLS section of the application server configuration file. Normally, the SQL in error appears to the user in a number of messages. If you consider this a security issue, add the following line to your application server config file:
Suppress SQL Error=1
When this line is set, SQL errors do not display details; instead, they refer the user to consult the system log. The detail that was in the SQL message is written to the log file.
See Also