Attribute _key on xtk.session.Write looks like ignored

[igorku]

Hello All,

• ACC 7.2.2 build 9349@e319b6d of 02/28/2022
• MSSQL is an external database
• <srcSchema> is (some values are masked/dotted):

<srcSchema ... entitySchema="xtk:srcSchema" img="xtk:schema.png" label="TestKey" ... name="TestKey" ... view="true" xtkschema="xtk:srcSchema">
  <createdBy _cs="..."/>
  <modifiedBy _cs="..."/>
  <element dataSource="..." label="TestKey" name="TestKey"
           sqltable="[dbo].[TestKey]">
    <attribute advanced="false" label="col1" name="col1" sqlname="col1" type="long"/>
    <attribute advanced="false" label="col2" name="col2" sqlname="col2" type="long"/>
    <key clustered="true" name="PK_TestKey">
      <keyfield xpath="@col1"/>
      <keyfield xpath="@col2"/>
    </key>
  </element>
</srcSchema>

Note, there is a <key> element defined in the schema! Two columns involved. It's important for later.

• MSSQL database table structure is:

create table dbo.TestKey
(
  col1 int not null,
  col2 int not null,
  constraint PK_TestKey Primary key(col1, col2)
)

• Test table data:

select * from dbo.TestKey;

col1 |	col2
------------
10   |  1
10   |  2
20   |  1
20   |  2

• Task: I need to remove all records from dbt.TestKey where col2 = 2 (two records to delete)
• JavaScript in use:

xtk.session.Write(<TestKey xtkschema="ef:TestKey" _operation="delete" _key="@col2" col2="2" />);

Note, here attribute @_key is different from the one defined in <scrSchema> - one column vs. two columns!

Problem

When I run the code, only one record is deleted from table. But expected two records to delete (WHERE col2 = 2).

I can grab the DELETE sql statement produced by the above code (SQL Server Profiler helped me). The statement is:

declare @p1 int
set @p1=42
exec sp_prepexec @p1 output,N'@P1 int,@P2 int',N'DELETE FROM [dbo].[TestKey] WHERE (col1 = @p1 AND col2 = @p2)',10,2
select @p1

Look at this - it uses columns col1 and col2 from the <key> defined in <scrSchema>, not the one defined in xtk.session.Write @_key attribute. Why? How to remove the records I need to remove?

Please help!

[david--garcia · Accepted solution]

Another approach in JS. https://experienceleague.adobe.com/developer/campaign-api/api/f-sqlExec.html

 var sql= "DELETE FROM TESTKEY WHERE COL2 = 2;
sqlExec(sql);

Features

Available in:

• Content management
• Delivery properties
• Typology rule
• JSSP
• SOAP Method
• WebApp
• Workflow

[igorku]

@david--garcia, Thank you for the response.

Yeah, I know this approach. But it looks like a workaround and it does not use TestKey data schema.

Actually, my TestKey data schema is created using External Account, so, table TestKey is not in Neolane database. Having this, and assuming that the external database is on the same server, I have to hard code database name in the DELETE query like this:

DELETE FROM <DatabaseName>.<schema>.TESTKEY WHERE COL2 = 2;

What if <DatabaseName> is different from environment to environment? E.g. on QA it's [QA_MyDBName], on PROD it's [PROD_MyDbName].

Or, what if it's even on different (compare to Neolane) server or data platform (say, Oracle, Snowflake, etc.). I expect that Federated Data Access (I use for my TestKey) works correctly.

Currently _key attribute is ignored and this looks like a bug in ACC 7.2.2. Not sure though. Trying to understand it.

[david--garcia]

Have you tried without specifying the key?

xtk.session.Write(<TestKey xtkschema="ef:TestKey" _operation="delete" col2="2" />);

[igorku]

I tried but no success

SCM-120008 Unable to find key values for element 'TestKey' (document with schema 'TestKey') XSV-350023 Unable to save document of type 'ef:TestKey'. SOP-330011 Error while executing the method 'Write' of service 'xtk:persist|xtk:session'.

[igorku]

@david--garcia, Thinking more I see that sqlExec is better than xtk.session.Write approach.

However, Is there a way to parametrize the DELETE query?

If I try to delete like this:

var sql = "DELETE FROM Marketing.dbo.TestKey WHERE col2 = $(l);"
sqlExec(sql, 2000);

I get this exception:

JST-310000 Error while compiling script '_webApp_APP164__preview' line 156: missing ; before statement (line='var sql = "DELETE FROM Marketing.dbo.TestKey WHERE col2 = serverForm.getLocalizableString("l");"
' token='l");"
').

Per docs (sqlExec) It looks like it's impossible to parametrize DELETE statement. Params work only for SELECT and UPDATE.

Q: How to avoid sql-injection issue for DELETE statement? Am I missing something?

Thank you!

[david--garcia]

Hi @igorku im not sure if is not available in the jsapi docs then I wouldnt know. On the other hand, if you want to find out the current database and schema name so that your code can work seamlessly in other environments, you can check with the following script (note is unfinished). but you can focus on the database environment you are working on and get the current db and current schema and use that to construct your database.schema base of your sql syntax.

var oracle = false;
var postgresql = false;
var sqlserver = false;

if (application.getConnection().isPostgreSQL) {
  postgresql = true;
  var sql = sqlGetString('SELECT current_database()');
} else if (application.getConnection().isOracle) {
  oracle = true;
  var sql = sqlGetString('SELECT * FROM v$database');
} else {
  var isSQLServer = sqlGetString('SELECT @@VERSION');

  if (isSQLServer.indexOf('Microsoft') != -1) {
    var sqlserver = true
    var sql = sqlGetString("SELECT DB_NAME()+'.'+SCHEMA_NAME()"); //returns database.schema --> i.e. adobe.dbo
  }

}

logInfo(sql);
logInfo("postgresql: " + postgresql + " oracle: " + oracle + " sqlServer: " + sqlserver)

[igorku]

This peace of code is useful. I will try.

Thank you, @david--garcia !