QueryParam

Used to verify or strongly type a query parameter to a valid SQL Type.

Component Signature

<bx:QueryParam value=[any]
sqltype=[string]
maxLength=[integer]
scale=[integer]
null=[boolean]
list=[boolean]
separator=[string] />

Attributes

Atrribute	Type	Required	Description	Default
value	any	false	The value of the parameter
sqltype	string	false	The SQL type of the parameter. One of: bigint, bit, blob, boolean, char, clob, date, decimal, double, float, int, integer, idstamp, longvarchar, money, numeric, real, smallint, string, time, timestamp, tinyint, varbinary, or varchar.
maxLength	integer	false	The maximum length of the parameter
scale	integer	false	The scale of the parameter, used only on double and decimal types. Defaults to null
null	boolean	false	Whether the parameter can be null or not.
list	boolean	false	Whether the parameter is a list or not
separator	string	false	The separator to use for the parameter. Defaults to a comma.

Examples

Use bx:queryParam to protect your application from SQL-injection attacks:

<bx:query name="pages">
    SELECT *
    FROM content
    WHERE id=<bx:queryparam value="#url.id#" />
</bx:query>

It is highly recommended to set the sqltype of the incoming data:

<bx:query name="pages">
    SELECT *
    FROM content
    WHERE id=<bx:queryparam value="#url.id#" sqltype="integer" />
</bx:query>

Using Lists in QueryParam

For SQL IN clauses with comma-separated param values, use list=true:

<bx:query name="pages">
    SELECT *
    FROM media
    WHERE type IN <bx:queryparam value="#url.mediaTypes#" list="true" sqltype="varchar" />
</bx:query>

Assuming url.mediaTypes is equal to book,magazine,newspaper, this will generate the following SQL statement:

SELECT *
FROM books
WHERE title IN (?,?,?)

Basic example

Shows how to use a bx:queryparam tag within bx:query.

<bx:query name="news">
    SELECT id,title,story
    FROM news
    WHERE id = <bx:queryparam value="#url.ID#" sqltype="integer">
</bx:query>

Using a list on an IN statement

Assumes url.idList is a comma separated list of integers, eg: 1,2,3

<bx:query name="news">
    SELECT id,title,story
    FROM news
    WHERE id IN (<bx:queryparam value="#url.IDLIST#" sqltype="integer" list="true">)
</bx:query>

Using an expressions to controll null values

Shows a basic example of using an expression to control whether null is passed to the queryparam

<bx:query name="test">
      INSERT into test ( key, value )
      VALUES(
            <bx:queryparam value="#key#" sqltype="varchar" null="#isNumeric( Key ) == false#">
            <bx:queryparam value="#value#" sqltype="varchar" null="#value == ""#">
      )
</bx:query>

script equivalent of bx:queryparam

Script syntax using queryExecute and struct notation

Run Example

exampleData = queryNew( "id,title", "integer,varchar", [ 
	{
		"id" : 1,
		"title" : "Dewey defeats Truman"
	},
	{
		"id" : 2,
		"title" : "Man walks on Moon"
	}
] );
result = queryExecute( "SELECT title FROM exampleData WHERE id = :id", {
	ID : 2
}, {
	DBTYPE : "query"
} );
writeOutput( result.TITLE[ 1 ] );

Result: Man walks on Moon

script equivalent of bx:queryparam

Script syntax using queryExecute and struct notation for multiple parameters

Run Example

exampleData = queryNew( "id,title", "integer,varchar", [ 
	{
		"id" : 1,
		"title" : "Dewey defeats Truman"
	},
	{
		"id" : 2,
		"title" : "Man walks on Moon"
	}
] );
result = queryExecute( "SELECT * FROM exampleData WHERE id = :id AND title = :title", {
	TITLE : {
		VALUE : "Man walks on Moon",
		sqltype : "varchar"
	},
	ID : {
		VALUE : 2,
		sqltype : "integer"
	}
}, {
	DBTYPE : "query"
} );
writeOutput( result.TITLE[ 1 ] );

Result: Man walks on Moon

script equivalent of bx:queryparam

script syntax using queryExecute and full array notation

Run Example

exampleData = queryNew( "id,title", "integer,varchar", [ 
	{
		"id" : 1,
		"title" : "Dewey defeats Truman"
	},
	{
		"id" : 2,
		"title" : "Man walks on Moon"
	}
] );
result = queryExecute( "SELECT title FROM exampleData WHERE id = ?", [
	{
		VALUE : 2,
		sqltype : "varchar"
	}
], {
	DBTYPE : "query"
} );
writeOutput( result.TITLE[ 1 ] );

Result: Man walks on Moon

script equivalent of bx:queryparam

script syntax using queryExecute and array shorthand

Run Example

exampleData = queryNew( "id,title", "integer,varchar", [ 
	{
		"id" : 1,
		"title" : "Dewey defeats Truman"
	},
	{
		"id" : 2,
		"title" : "Man walks on Moon"
	}
] );
result = queryExecute( "SELECT title FROM exampleData WHERE id = ?", [
	2
], {
	DBTYPE : "query"
} );
writeOutput( result.TITLE[ 1 ] );

Result: Man walks on Moon