Please enable JavaScript to view this site.

Navigation: Advanced topics > Programming topics > Database API > Methods

Database API: PrepareSQL()

Scroll Prev Next More

 

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)

Arguments

$SQL

a query with variables.

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 PHP function or variable.

 

 

Note: 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'

See also:

Database API: Exec()

Database API: Query()

Database API: Select()

Using SQL variables

Insert custom button

Field events

QueryResult object: fetchNumeric()

QueryResult object: value()

QueryResult object: fetchAssoc()

Tri-part events

About Database API

 

Created with Help+Manual 7 and styled with Premium Pack Version 3 © by EC Software