Overview of Advantage SQL Queries
|
Advantage SQL statements are text-based commands that you use to instruct ADS to perform some operation. Examples of operations that you can perform with Advantage SQL include creating and destroying (dropping) tables, adding or removing (dropping) indexes, inserting data into a table, deleting records from a table, updating data in one or more records in a table, adding permissions for users or groups, and selecting records to return to your client application, to name a few. These instructions are performed entirely on the server.
Many of the data access mechanisms for ADS provide alternative ways of requesting these same operations. For example, in Delphi, you can perform all of the preceding tasks using the Advantage TDataSet Descendant components. Similarly, any development environment that can directly access the ACE (Advantage Client Engine) API—such as Visual Basic 6, Delphi, Visual C++, C++Builder, and the like—can perform these tasks through the functions of the ACE API. Refer to the ADS help for information on the ACE API.
While some data access mechanisms permit you to manipulate data using means other than SQL, Advantage SQL is the common denominator. Specifically, SQL can be used with every data access mechanism that ADS supports (except with the Advantage Clipper RDDs). For example, ODBC (open database connectivity) is a SQL-based standard for creating client/server applications. Any language that supports ODBC can be an effective tool for writing client applications that use ADS, even if that language cannot use the ACE API.
When used with ADS, Advantage SQL statements are executed on the remote database server. If you are using ALS (Advantage Local Server), the queries are executed on the client machine. To put this another way, only with ADS do you get the full benefits of distributed computing with SQL queries.
Although ALS does not provide a client application with the benefit of executing SQL statements on the server, there are still situations in which SQL is better suited or even required with ALS:
-
With some of the ADS data access mechanisms, SQL provides the only mechanism for executing stored procedures.
-
SQL provides a mechanism for joining tables from multiple data dictionaries with a single connection.
-
SQL reduces the amount of coding required to perform some complex tasks, especially if tasks involve multiple tables.
-
SQL makes code more portable to other RDBMS (relational database management systems).
SQL is a parsimonious language for performing many tasks. Even environments that support alternative ways of working with ADS data can exploit the powerful nature of Advantage SQL. For example, even though Delphi developers find the TAdsTable immensely useful, there are many times when using Advantage SQL through a TAdsQuery component performs a task more efficiently.
Advantage SQL queries consist of table references, field references, aliases, keywords, literals, operators, SQL scalar functions, comments, and parameters. Each of these is discussed in the following sections.
| Note | In this book, we have adopted a convention of showing SQL keywords and table names in uppercase and field names with initial capital letters. However, with the exception of character string literals, SQL statements are not case sensitive. As a result, SELECT * FROM Customer, select * from CUSTOMER, and select * FROM Customer are equivalent. The exception to this rule is in Linux, which is a case-sensitive operating system. In Linux, table names are case sensitive, which is why we use uppercase for table references since they match our sample database filenames. |
Field Names and Table Names
Almost every SQL statement refers to at least one table, and most refer to one or more fields. For example, consider the following statement:
SELECT Picture FROM PRODUCTS
Picture in this statement refers to the Picture field, and PRODUCTS refers to the PRODUCTS table.
Whether a reference is interpreted by ADS to be a field name or a table name depends on the location of the reference in the statement. In the preceding statement, Picture is interpreted to be the field name because field names, but not table names, can appear in this location of the SELECT statement. By the same token, PRODUCTS is interpreted to be the table name, since table names are expected in the FROM list of the SELECT statement.
When a field name or table name begins with a numeral, or contains characters other than 0–9 and A–Z, the name must be enclosed between either double quotes or square braces [ ]. For example, if you want to include the Product Name field in the preceding query, you enclose it in delimiters as follows:
SELECT Picture, "Product Name" FROM PRODUCTS
Likewise, the following query is equivalent:
SELECT Picture, [Product Name] FROM PRODUCTS
ADS does not care which delimiter pair you use. Which you choose to use will often depend on the development environment you are using. For example, in C# you must escape double-quotation characters in string literals, preceding the double- quotation character with a backslash. Square braces, however, do not need to be escaped. Consequently, for readability, we prefer
oCommand.CommandText = "DELETE FROM CUSTOMER " + "WHERE [Customer ID] IN (SELECT [Customer ID] FROM __old)";
over
oCommand.CommandText = "DELETE FROM CUSTOMER " + "WHERE \"Customer ID\" IN (SELECT \"Customer ID\" FROM __old)";
Field Names and Table Aliases
It is not uncommon to reference field names from two or more tables in a single SQL statement. For example, consider the following query:
SELECT "Customer ID", "Employee ID", "Last Name" FROM INVOICE, EMPLOYEE WHERE "Employee ID" = "Employee Number"
This is a valid query because the field names in the SELECT list and the WHERE clause are unique to the tables listed in the FROM list. Nonetheless, this query is a bit more difficult to read unless you know for certain that Employee ID is a field of the INVOICE table and Employee Number is a field of the EMPLOYEE table.
If the field names are not unique, the problem is more serious. Consider the following query:
SELECT "Invoice No", "First Name", "Last Name" FROM INVOICE, CUSTOMER WHERE "Customer ID" = "Customer ID"
This is an invalid query, since the references to "Customer ID" in the WHERE clause are ambiguous. In cases like these, you have two options. The first is to use dot notation to qualify the field name with the table name. For example, the preceding query can be rewritten to look like the following:
SELECT INVOICE."Invoice No", CUSTOMER."First Name", CUSTOMER."Last Name" FROM INVOICE, CUSTOMER WHERE INVOICE."Customer ID" = CUSTOMER."Customer ID"
The drawback to this approach is that table names are often not short. As a result, using this technique may result in a lot of extra typing. Instead of using the entire table name, you can define aliases for the table name in the FROM clause of the query, and then qualify the field names using the aliases. This is demonstrated in the following query:
SELECT Inv."Invoice No", Cust."First Name", Cust."Last Name" FROM INVOICE Inv, CUSTOMER Cust WHERE Inv."Customer ID" = Cust."Customer ID"
Here, each table is associated with an alias, and that alias is used in dot notation to qualify the field references. As a result, there is no ambiguity, making this a valid query.
Now consider the following query:
SELECT Inv."Customer ID", Inv."Employee ID", Emp."Last Name" FROM INVOICE Inv, EMPLOYEE Emp WHERE Inv."Employee ID" = Emp."Employee Number"
While the aliases in the preceding query were not required, they produced a more readable query, one that will be easier to read, and hence, maintain, in the long run.
Keywords
Keywords are special words that are used to build the primary instructions embodied in a SQL statement. For example, consider the following SQL command:
SELECT Picture FROM PRODUCTS
The keywords in this SQL statements are SELECT and FROM. These words hold special meaning to ADS, and are used to drive the requested operations.
Keywords appear directly in your SQL statements, and are never enclosed within delimiters. Some SQL keywords are also reserved. As you learned in the preceding section, field names and table names that begin with a letter and that consist of only alphanumeric characters also do not require delimiters. However, if you have a field name or a table name that matches one of the reserved keywords, you must enclose that field name or table name in the double-quote or square brace delimiters. Doing so prevents ADS from confusing that field name or table name with the matching reserved keyword. For example, imagine that in the ITEMS table the Quantity field name was changed to Count. The following query would be invalid:
SELECT Count FROM ITEMS
When parsing this SQL statement, ADS will interpret Count to be the COUNT reserved keyword, which will cause an error because the keyword COUNT cannot be used like this. In order to distinguish between keywords and field or table names, use delimiters. If the ITEMS table had a field named Count, the following query would be parsed correctly:
SELECT "Count" FROM ITEMS
As is the case with field names that contain special characters, you can use square braces as the delimiters instead of double-quotation marks, as shown in the following query:
SELECT [Count] FROM ITEMS
The reserved keywords, as of the time of this writing, are listed in Table 9-1. ADS follows the ANSI (American National Standards Institute) SQL 92 standard when designating the reserved keywords. If a word is a keyword in the ANSI SQL 92 standard but not in the list of the reserved keywords shown in Table 9-1, it is still a good idea to enclose the keyword in double quotes or square braces. As ADS implements additional features, more keywords from the ANSI standard will be added to the list. Enclosing the words in double quotes or square braces will ensure that the SQL statement will work in future versions of ADS as well as other RDBMSs.
| ADD | ALL | ALTER | AND | ANY |
| AS | ASC | AVG | BEGIN | BETWEEN |
| BY | CASE | COLUMN | COMMIT | CONSTRAINT |
| COUNT | CREATE | DEFAULT | DELETE | DESC |
| DISTINCT | DROP | ELSE | END | EXECUTE |
| EXISTS | FALSE | FROM | GRANT | GROUP |
| HAVING | IN | INDEX | INNER | INSERT |
| INTO | IS | JOIN | KEY | LEFT |
| LIKE | MAX | MIN | NOT | NULL |
| ON | OR | ORDER | OUTPUT | OUTER |
| PRIMARY | PROCEDURE | REVOKE | ROLLBACK | SELECT |
| SET | SQL | SUM | TABLE | THEN |
| TO | TRANSACTION | TRUE | UNION | UNIQUE |
| UPDATE | USER | VALUES | VIEW | WHEN |
| WHERE | WORK |
SQL Literals
Literals are explicit representations of data in expressions. They are used in SQL statements to represent data that does not change. How you specify a literal depends on the type of expression. String literals, for example, are enclosed in single quotation marks. Integer literals, by comparison, are numerals without delimiters. Consider the following statement:
SELECT * FROM INVOICE WHERE "Invoice Due Date" > "Invoice Date" + 60
In this query, all invoices where customers were given more than 60 days to pay are selected. The 60 in this query is a literal integer value.
The following is an example of a query with a string literal:
SELECT "Customer ID" FROM CUSTOMER WHERE State = 'CA'
This query selects the Customer ID field from the CUSTOMER table for all customers whose State field contains an exact match to the characters CA.
There are six literal types that you can use in Advantage SQL statements. These are Boolean, date, numeric, string, time, and timestamp. The rules for representing these literals are described in the following sections.
Boolean Literals
The Boolean value True is represented as 1 and a Boolean False is represented as 0. You can use the keywords TRUE instead of 1 and FALSE instead of 0.
Date Literals
Literal date values are enclosed in single quotes, and use the current date format setting (the default is mm/dd/ccyy) or the ANSI date format (ccyy-mm-dd), which will always work in ADS regardless of the current date format setting. Unless you use the default or ANSI setting, you set this format for your client applications programmatically using the ACE API AdsSetDateFormat function, or the Advantage TDataSet Descendant’s TAdsSettings DateFormat property.
You can change the date format used for viewing data in the Advantage Data Architect by selecting Tools | Environment Settings from the main menu, which displays the ARC Env Settings dialog box. Note that this will not change the date format used in your applications. Look at a date field in the Table Browser to determine the current date format setting used for viewing dates within the Advantage Data Architect.
Note that you must represent the month and day with two digits (use a leading zero for single digits). For example, if the current date format is ccyy-mm-dd, the correct representation of January, 1, 2003, is '2003-01-01'. If your dates appear using the mm/dd/ccyy format, a valid date literal is '04/15/2005'. If dates appear using the ccyy.mm.dd format, you represent the same date using the '2005.04.15' literal.
Numeric Literals
Both integer and floating-point numbers are represented by numerals without delimiters. These numerals may be preceded by either a plus character (+) or a minus character (–) to denote sign. Floating-point values use a period to separate the whole number part from the decimal part. You cannot use a comma as a decimal point.
When the numbers you need to represent are very large, you can express numeric literals using exponential notation, sometimes referred to as scientific notation. Exponential notation specifies a number using two parts, the mantissa and the exponent. The mantissa is a number with one significant digit and some number of decimals. The exponent denotes the power of 10 to which the mantissa will be raised. Either the letter e or E separates the mantissa and the exponent.
If the exponent is a positive number, it indicates how many positions the decimal point of the mantissa should be shifted to the right; if the exponent is a negative number, it specifies how many decimal places the decimal point should be shifted to the left.
The following values are examples of numeric literals:
1 1.0 -3456.43 3454324.93783628 7.43e12 -4.087e6
String Literals
A string literal is a sequence of one or more characters enclosed in single quotation marks. Any printable character can appear in a string literal. However, if your string literal includes the single quotation mark character (’), you precede it with a single quotation mark. The following are examples of string literals:
'Robert "Bob" Jefferson' 'That''s the truth, the whole truth, and nothing but the truth' 'a' 'San Francisco'
With one exception, string literals are case sensitive. Specifically, the following three string literals are not equivalent: 'laser', 'Laser', and 'LASER'. The exception is string literals used with the CONTAINS scalar function on case-insensitive FTS (full text search) indexes.
In all other cases, if you want to perform a case-insensitive comparison between a string literal and another string value (either a string field reference, a string expression, or another string literal), you must use the UCASE or LCASE scalar functions to convert both parts of the comparison to a common case. SQL scalar functions are described later in this chapter.
Time Literals
Time literals are enclosed in single quotation marks, and use one of the following four formats: HH:MM, HH:MM AM (or PM), HH:MM:SS, or HH:MM:SS AM (or PM). If the AM (or PM) is missing from the literal, 24-hour time is assumed. The AM/PM part of time literals is not case sensitive. The following are valid time literals:
'19:10' '4:43 AM' '9:00:45 pm' '22:19:59'
Timestamp Literals
A timestamp literal denotes a specific date and time, and can be accurate to the millisecond. Timestamp literals, like time literals, are enclosed in single quotation marks. The date part of a timestamp uses the date format as defined earlier in the “Date Literals” section. The time part of the timestamp literal uses the 24-hour HH:MM:SS time format, where seconds are required, but may optionally include milliseconds (HH:MM:SS.mmm). The date part and the time part are separated from each other using a single space. If your date format is mm/dd/ccyy, the following are valid stamp literals:
'7/6/2004 04:52:34' '12/31/2006 23:59:59.999'
If your date format is ccyy/mm/dd, the following are valid timestamp literals:
'2004/7/6 04:52:34' '2006/12/31 23:59:59.999'
The ANSI date format (ccyy-mm-dd) is always supported by ADS, regardless of the current date format setting. The following are valid timestamp literals regardless of current date format:
'2004-07-06 04:52:34' '2006-12-31 23:59:59.999'
Other Literal Types
Not all data types can be represented literally in your SQL statements. For example, there is no raw literal. If you want to include a literal in your SQL statements, but there is no literal for the data you want to represent, you must convert one of the available literal types to the data type you need.
You perform data type conversions using the Convert SQL scalar function. When you invoke Convert, you pass two parameters. The first is a literal representation of your data using one of the available literal types, and the second is a SQL data type that you want that literal converted to. For example, the following is how you represent a literal raw value:
Convert ('{B54F3741-5B07-11cf-A4B0-00AA004A55E8}', SQL_BINARY)
Operators in SQL Statements
Operators are used to create or modify expressions. Unary operators, such as the integer – (negation) and the Boolean NOT (negation), modify an operand. Binary operators, such as the string + (concatenation) and the Boolean = (equals), create a new expression by combining two operands.
The process of applying an operator to an expression is referred to as expression evaluation, and the result is also an expression of some type. For example, the following is an expression:
1 + 1
In this case, two expressions, each a numeric literal, are combined through addition. The result is a single numeric expression with the value of 2.
Expression evaluation normally proceeds from left to right. Consider the following expression using the addition operator (+), in which 1 and 1 are added together to make 2, and then 2 is added to 5 to make 7:
1 + 1 + 5
But not all operators have the same precedence. Operations involving operators of a higher precedence are executed first, followed by operators of lower precedence. When two operators have the same precedence, the operators are evaluated from left to right. For example, the multiplication operator (*) has a higher precedence than the addition operator. Consider the following expression:
1 + 1 * 5
The result of this expression is 6. The multiplication is executed first, resulting in a value of 5. Next, the addition operator adds 1 to 5, resulting in 6.
You can control precedence by using parentheses. Operations within parentheses are always performed as a unit. For example, the following expression evaluates to 10. First, 1 is added to 1 to get 2. Next, 2 is multiplied by 5 to produce 10.
(1 + 1) * 5
The operators available in Advantage SQL are described in the following sections. These sections are divided by the type of expressions that the operators apply to. Within each section, the operators are further divided by operator type, ordered by operator type precedence.
Boolean Operators
There are three Boolean operator types. These are logical comparison operators, the logical complement operator, and logical operators.
Logical Comparison Operators
Logical comparison operators are binary operators that employee two logical operands, and that result in a Boolean expression. The logical comparison operators are = (equals), <> (not equals), IS NULL, and IS NOT NULL. For both IS NULL and IS NOT NULL, the first operand must be a field name reference.
Logical Complement Operator
The logical complement operator is NOT. Use it to convert a Boolean True to False, and a Boolean False to True.
Logical Operators
There are two logical operators—AND and OR—and both require two Boolean operands. An AND operation results in a Boolean True if, and only if, both operands are True, and results in False otherwise. An OR operation evaluates to a Boolean True if at least one of the operands is a Boolean True, and evaluates to False only if both operands are False.
Numeric Operators
There are three categories of numeric operators. These are numeric sign change operators, numeric arithmetic operators, and numeric comparison operators.
Numeric Sign Change Operators
Numeric sign change operators are unary operators, and there are two of them: – and +. In reality, only the – sign change operator has utility. Precede a numeric operand with a - to change the sign of the numeric expression.
Numeric Arithmetic Operators
There are four numeric arithmetic operators: * (multiplication), / (division), + (addition), – (subtraction). Each of these operators requires two numeric operands, and result in a numeric expression. If at least one of the operands is a floating-point expression, the result will be a floating-point expression. Within this type, * and / have higher precedence than + and –.
Numeric Comparison Operators
The numeric comparison operators are > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), IN, NOT IN, BETWEEN, NOT BETWEEN, IS NULL, and IS NOT NULL. Numeric comparison operators require two numeric operands and evaluate to a Boolean expression.
String Operators
There are two types of string operators: the string concatenation operator and string comparison operators.
String Concatenation Operator
The string concatenation operator (+) is used to combine two string operands, producing a string expression. The resulting string expression contains all characters in the first operand plus all characters in the second operand.
String Comparison Operators
The string comparison operators are > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), LIKE, NOT LIKE, IN, NOT IN, BETWEEN, NOT BETWEEN, IS NULL, and IS NOT NULL. These are binary operators that evaluate to a Boolean expression. The use of LIKE, IN, and BETWEEN is discussed in Chapter 10.
Date Operators
There are two types of date operators: date arithmetic operators and date comparison operators.
Date Arithmetic Operators
Date arithmetic operators are binary operators where one operand is a date expression and the other is an integer expression. Date arithmetic operators evaluate to a date expression. The date arithmetic operators are + (addition) and – (subtraction). Use the + operator to calculate a date some specified number of days in the future, and the – operator to calculate a date some specified number of days in the past.
Date Comparison Operators
Date comparison operators are binary operators that compare two date operands and evaluate to a Boolean expression. The valid date comparison operators are > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), <> (not equal to), BETWEEN, NOT BETWEEN, IS NULL, and IS NOT NULL.
Time and Timestamp Operators
There is only one type of operator that applies to time and timestamp expressions. These are the time comparison operators.
Time Comparison Operators
Time comparison operators are binary operators that evaluate to a Boolean expression. The time operators are > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), = (equal to), and <> (not equal to). The >, <, >=, <=, and <> operators require two time or timestamp operands.
Other Operators
Fixed-length and BLOB (binary large object) expressions can be used with comparison operators. Fixed-length binary expressions can employ the > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), = (equal to), <> (not equal to), IN, NOT IN, IS NULL, and IS NOT NULL operators. These are binary operators that take two fixed-length binary operands and evaluate to a Boolean expression.
Variable-length binary (BLOB) fields support two comparison operators: IS NULL and IS NOT NULL. These unary operators take one BLOB field operand and evaluate to a Boolean expression.
SQL Scalar Functions
SQL scalar functions are built-in subroutines that can be used in your expressions. These functions permit you to write more flexible SQL statements by performing transformations on data or returning values that can only be determined at runtime.
| Note | A scalar function is one that returns an expression. |
For example, the following SQL statement uses the CURDATE() function, which returns the current date, based on your server’s internal clock:
SELECT * FROM INVOICE WHERE "Invoice Due Date" > CURDATE() - 7 AND "Invoice Due Date" < CURDATE() AND "Date Payment Received" IS NULL
This query will return all invoices that became due in the past week for which payment has not yet been received. Because of the use of the CURDATE() function, this query will always produce the past week’s outstanding invoices, whether you execute it today, tomorrow, or two years from now.
The Advantage SQL scalar functions can be divided into four categories. These are date/time functions, math functions, string functions, and miscellaneous functions. Each of these is described briefly in the following sections. For a more detailed description of each function, including its syntax, refer to the ADS help.
Date/Time Functions
You use the Advantage date/time SQL scalar functions to determine or process date/time information. Table 9-2 contains a list of the supported date/time functions.
| CURDATE() | CURRENT_DATE() | CURRENT_TIME() |
| CURRENT_TIMESTAMP() | CURTIME() | DAYNAME() |
| DAYOFMONTH() | DAYOFWEEK() | DAYOFYEAR() |
| EXTRACT() | HOUR() | MINUTE() |
| MONTH() | MONTHNAME() | NOW() |
| QUARTER() | SECOND() | TIMESTAMPADD() |
| TIMESTAMPDIFF() | WEEK() | YEAR() |
Math Functions
The Advantage math scalar functions provide you with a rich collection of arithmetic and trigonometric functions. Also included in this category are functions that round and truncate numbers, as well as a function that generates random numbers. The Advantage math SQL scalar functions are listed in Table 9-3.
| ABS() | ACOS() | ASIN() |
| ATAN() | ATAN2() | CEILING() |
| COS() | COT() | DEGREES() |
| EXP() | FLOOR() | LOG() |
| LOG10() | MOD() | PI() |
| POWER() | RADIANS() | RAND() |
| ROUND() | SIGN() | SIN() |
| SQRT() | TAN() | TRUNCATE() |
String Functions
The Advantage string functions are used to process and convert string expressions. The Advantage string SQL scalar functions are listed in Table 9-4.
| ASCII() | BIT_LENGTH() | CHAR() | CHAR_LENGTH() |
| CHARACTER_LENGTH() | CONCAT() | INSERT() | LCASE() |
| LEFT() | LENGTH() | LOCATE() | LOWER() |
| LTRIM() | OCTET_LENGTH() | POSITION() | REPEAT() |
| REPLACE() | RIGHT() | RTRIM() | SPACE() |
| SUBSTRING() | UCASE() | UPPER() |
Miscellaneous Functions
The miscellaneous functions are those that do not fall into one of the other categories. Among other things, they can be used to return the name of the user whose connection the SQL query is being executed over, the name of the database being queried, several scalar conditional functions, IFNULL(), ISNULL(), and IIF(), as well as some functions that are used in conjunction with full text search. The Advantage miscellaneous SQL scalar functions are listed in Table 9-5.
| CONVERT() | DATABASE() | DIFFERENCE() |
| SOUNDEX() | IFNULL() | ISNULL() |
| USER() | IIF() | CONTAINS() |
| SCORE() | SCOREDISTINCT() |
Parameters in SQL Statements
A parameterized query is one that includes one or more parameters. A parameter is a placeholder for a value in the WHERE clause of SELECT, UPDATE, or DELETE queries. The value of a parameter does not need to be defined until just before the query is executed by the client application, permitting you to define the query at design time and then customize which records it will affect at runtime.
In addition to permitting you to create customizable query templates, parameterized queries offer potential performance benefits as well. Specifically, if you need to execute a parameterized query more than once, even when one or more of the parameter values change, the query will generally execute faster upon repeated execution.
There are two sources of this performance benefit. The first is that ADS need only parse and check the syntax of the query the first time it is executed. So long as the SQL statement being executed is unchanged, excluding the values of the parameters, subsequent executions do not need parsing and syntax checking. Second, upon repeated execution of a parameterized query, only the parameter values need to be sent to the server. The remainder of the query does not, having already been sent during a previous execution.
There are two types of parameters that can appear in a parameterized query: named parameters and positional parameters. Named parameters are identified by placeholders that appear as a text label preceded by a colon. For instance, in the following SQL statement, the parameter is named custparam:
SELECT City, State FROM Customer WHERE [Customer ID] = :custparam
Positional parameters do not have labels. Instead, they are represented by a question mark (?). The following SQL statement is similar to the preceding one, except that it uses a positional parameter rather than a named parameter:
SELECT City, State FROM CUSTOMER WHERE [Customer ID] = ?
In order to execute a parameterized query, it is necessary to supply a value for each parameter prior to the execution of the query. How this is done depends on the development environment you are writing your client applications in. Using parameterized queries, and assigning values to parameters at runtime, is described for each of the ADS-supported data access mechanisms in the later chapters of this book. Refer to the chapter that discusses the data access mechanism you want to use for details.
Comments
You use comments in a SQL statement to document the statement’s purpose as well as temporarily disable one or more parts of the statement. When ADS is executing the query, it ignores all comments. ADS supports three types of comments in SQL statements. Of these, two are single-line comments, and the third is a multiline comment.
A single-line comment instructs ADS to ignore all text to the right of the comment indicator. There are two single-line comment indicators: // (two consecutive forward slashes), and -- (two consecutive dashes). The following is an example of a SQL statement containing a single-line comment:
SELECT * FROM INVOICE //WHERE "Customer ID" IS NULL
In this example, the WHERE clause has been temporarily disabled. This type of comment can be useful while you are testing a SQL statement. Here is another example:
// Delete the customer records where the // customer has already been processed DELETE FROM CUSTOMER WHERE [Customer ID] IN (SELECT [Customer ID] FROM PROCESSED)
This time, the comment is designed to document what task the query is performing.
Multiline comments are enclosed between two delimiters. The comment begins with the /* (forward slash, asterisk) delimiter and concludes with the */ (asterisk, forward slash) delimiter. The following shows how this comment could have been used in the preceding query:
/* Delete the customer records where the customer has already been processed */ DELETE FROM CUSTOMER WHERE [Customer ID] IN (SELECT [Customer ID] FROM PROCESSED)
Unlike single-line comments, which comment out everything to the right of the comment indicator, a multiline comment can begin and conclude within a single line. For example, the following query will select only one field:
SELECT "Customer ID"/*, State*/ FROM CUSTOMER
In this query, the field name State has been commented out. This use of the multiline comment is quite valuable while you are testing your queries.
SQL Scripts
Since ADS 7.0 and later, you can use SQL scripts anywhere a simple SQL statement is expected, except within View definitions. A SQL script is two or more SQL statements separated by semicolons. SQL scripts permit you to package several SQL statements together when you send them to the server. They are particularly useful when you must communicate to the server over a slow connection, thereby reducing the number of round-trips required to complete a complicated task.
While SQL scripts permit you to send two or more queries to ADS in a single statement, they introduce a potential security risk that you must be aware of. Specifically, if you construct a query at runtime from data input by a user, you must check the user’s input to prevent the unwanted introduction of semicolons. If a user enters a semicolon, and you subsequently concatenate that entry with your SQL query, ADS will interpret the semicolon as a SQL statement separator.
Initially, this might not sound like a security threat. After all, wouldn’t the presence of the semicolon surely lead to a syntax error, causing ADS to return an error code? The answer is “It depends.” A user aware of the use of semicolons to separate SQL statements, and knowledge of SQL, could exploit this feature to undermine your database.
Here’s an example. Imagine that your client application includes a query that is constructed at runtime based on the user’s entry of a Customer ID. The following is an example of how a query might be created from a user’s input. This example is written in Delphi:
//Construct the query AdsQuery1.SQL.Text := 'SELECT * FROM CUSTOMER ' + 'WHERE [Customer ID] = ' + Edit1.Text; //Execute the query AdsQuery1.Open;
Here, the value entered into the input field named Edit1 is concatenated to the query being assigned to the SQL property of an AdsQuery. Now consider what would happen if the user enters the following data into the edit:
12037; DROP TABLE CUSTOMER; DROP TABLE ITEMS
The resulting query would actually be a SQL script, and would look like the following:
SELECT * FROM CUSTOMER WHERE [Customer ID] =12037; DROP TABLE CUSTOMER; DROP TABLE ITEMS
Obviously, if you were to execute this SQL script, it could seriously compromise your database.
The easiest way to prevent this from being a problem is to use parameterized queries where you bind the user’s input to query parameters. Because of the way parameterized queries are executed, ADS will never confuse a semicolon in a parameter as a statement separator.
If it is not feasible to use parameterized queries when building SQL statements based on user input, you can do several things. First of all, limit the amount of text that the user can enter. If a Customer ID is never more than ten characters, don’t accept more than ten characters. Similarly, if the input is necessarily an integer value, verify that the user entered an integer. Second, examine the contents of the user’s input. If it contains a semicolon, or any other element that it should not, reject the entered data.
In the end, the most important point that you need to be aware of is that semicolons can be abused. You need to keep this in mind when working with user input that will contribute to a SQL statement.
|
EAN: 2147483647
Pages: 129