How to Use SqlExec in Adobe Campaign Classic

SqlExec is a JSSP function in Adobe Campaign Classic that allows you to run raw SQL queries directly on the database. It supports INSERT, UPDATE, DELETE, and even SELECT statements—though it only returns the number of records affected, not the actual data.

To use SqlExec in Adobe Campaign Classic, your operator account must have the SQL SCRIPT EXECUTION right enabled. This permission—labeled sql in the rights configuration—grants the ability to execute raw SQL scripts directly on the database.

Without this right, attempting to use SqlExec() will result in a permissions error.

But hey… 🤫 let’s keep this between us.

There’s been a longstanding, quietly ignored security loophole in some deployments that technically allows a savvy operator to escalate privileges to admin. Yes, it’s a security issue. Yes, it’s been known for years. No, it still hasn’t been patched in some on-prem or poorly configured instances.

Now that you’re admin let’s do some work..

Syntax

var count = SqlExec(query [, value1, value2, …]);

Parameters

• query (string): your SQL statement, with $(…) placeholders for any parameters.
• value1, …: JavaScript values that will be bound to each placeholder, in order.

Return value

Returns the number of records updated or inserted in case of an UPDATE or INSERT SQL command. In case of select it returns number of records.

Substitution Variable Types

When constructing parameterized SQL queries using SqlExec, Adobe Campaign provides a set of variable placeholders. These ensure that the correct SQL data type is used when binding JavaScript variables to the SQL statement.

Placeholder	Description
$(sz)	string
$(m)	memo (long text)
$(l)	long (64-bit integer)
$(s)	short (16-bit integer)
$(b)	byte (8-bit integer)
$(d)	double
$(f)	float
$(ts)	timestamp
$(dt)	date
$(tm)	time
$(dur)	timespan in milliseconds

Auto-substitution placeholders

These placeholders do not require a value from JavaScript—they are automatically populated by the system. They can be used only with INSERT or UPDATE:

Placeholder	Description	Operation
$(id)	Auto-generates a unique ID from the default sequence	INSERT
$(id:MySeqName)	Generates ID using a custom named sequence	INSERT
$(curdate)	Inserts current timestamp (date and time)	INSERT, UPDATE

Examples

var sql = "INSERT INTO events (eventName, startTime, duration) VALUES ($(sz), $(ts), $(dur))";
SqlExec(sql, "Webinar", new Date(), 5400000);

Using auto primary key and current date as placeholder variable

var sql = "INSERT INTO logs (logId, created, userId) VALUES ($(id), $(curdate), $(l))";
SqlExec(sql, 2023);

For more records to process you can use loops.

for each (var client in clientList) {
  var sql = "INSERT INTO Reporting (sClientdesc, sClient) " +
            "VALUES ('" + client.description + "', '" + client.clientCode + "')";
  sqlExec(sql);
}

Here are some examples of using SqlExec to update or remove data from lists in Adobe Campaign—a task that can be quite tedious when done through standard workflow activities.

Share With Others