Database API: PrepareSQL()

 

This function prepares an SQL query when you use SQL variables in it. SQL variables are case-insensitive.

 
Read more about using Using SQL variables.

Syntax

DB.PrepareSQL(SQL)

DB.PrepareSQL(SQL, ...)

Arguments

SQL

a query with variables

...

Additional parameters. See Example 3 for more info.

Return value

The prepared SQL query.

Example 1

In the AfterAdd event, you can use:

 

sql = DB.PrepareSQL("insert into log (lastid) values (:new.id)");
DB.Exec( sql );

Example 2

You can use this code in the Server event of a Custom Button or in Field events for the View/Edit pages:

 

sql = DB.PrepareSQL("insert into log (lastname) values (':name')");
DB.Exec( sql );

Example 3

You can also use several variables within the function:

 

sql = DB.PrepareSQL("select * from customers where username=':session.userid' and age>:1 and last_name=':2'", 20, "smirnoff");

 

The resulting query will look like this:

 

select * from customers where username='jsmith' and age>20 and last_name='smirnoff'

 

Note: the 20 and 'smirnoff' in the example above can be replaced with any C#/VB.NET function or variable. This function will also protect you from "bad" characters and SQL injection.

 

Imagine the situation where we need to select all customers where last name is O'Rourke. If we simply build the SQL query and concatenate the value with an apostrophe, our SQL will break not to mention that it opens the doors welcoming SQL injection. PrepareSQL function takes care of this.

 

 

sql = DB.PrepareSQL("select * from customers where last_name=':1'", "O'Rourke");

 

This will produce a proper SQL query with apostrophe character properly screened.

 

select * from customers where last_name='O\'Rourke'

 

If you do not use single quotes in your SQL query, then the variable will be treated as a number. For instance, the following code:

 

 

sql = DB.PrepareSQL("select * from customers where and age>:1", 20);

 

 

will produce the following SQL:

 

 

select * from customers where age>20

 

 

And you pass a text value in this situation:
 

sql = DB.PrepareSQL("select * from customers where and age>:1", "some text");

 

 
it will be converted to 0 like this. This is also a SQL injection prevention measure.

 

 

select * from customers where age>0

See also:

•Database API: Exec()

•Database API: Query()

•Database API: Select()

•Using SQL variables

•Insert custom button

•Field events

•Preventing SQL injection in web applications