Hi Michael

I shall look into problems due to DisableBind=0 later on. In order to solve the immediate problem I created another transaction object using same parameters as SQLCA and just replaced the disablebind=1 with disablebind=0 in the connectstring and used the object just for this update of greek letters string. It seems to work fine even at the customer place even without NcharBind=1 but anyhow I added it in the Connectionstring for safety sake. Strangely it was failing yesterday when I used SQLCA with a connectionstring containing DisableBind=0 and without NcharBind for the updation of greek string.

Thanks for all your help. It is always helpful to be able to discuss with some one.

Regards

Vinay

Hi Michael

Now I have tried to put a breakpoint in sqlpreview event of the transaction object and on my machine it shows the same correct sqlsyntax as below in both the cases (disablebind=1 and 0). However it saves correct letters only when I have disablebind= 0. I need to try it at customer place.

update cu168_riskfras_lang set riskfras_beskrivning = N''Υγρό και ατμοί πολύ εύφλεκτα..'' WHERE serial_number = 1083;

I tried also to log the SQL syntax in the sqlpreview event into another table. 

It logs as below when disablebind = 0

execute sp_execute_sql @as_sql =' update cu168_riskfras_lang set riskfras_beskrivning = N''Υγρό και ατμοί πολύ εύφλεκτα..'' WHERE serial_number = 1083;'

It logs as below when disablebind = 1

execute sp_execute_sql @as_sql =' update cu168_riskfras_lang set riskfras_beskrivning = N''???? ?a? atµ?? p??? e?f?e?ta..'' WHERE serial_number = 1083;'

Is there some way I can get it to work with disablebind=1?

Thanks

Vinay

How to trace SQL in your PB app.

Transaction has SQLPreview and DBError events.
SQLPreview triggers just before SQL is sent to the database.
It triggers for both embedded SQL and SQL from DataWindow/DataStore.

What I typically do in any app where I start from no code at all:

1. Inherit from Transaction and name this class tr_base.
2. Application > Additional Properties > Variable Types > assign SQLCA := tr_base
3. In tr_base add following code and breakpoint
   

   tr_base == event SQLPreview(...)
   int i
   i = 0   // <== Add debug breakpoint on THIS line!
   

4. Start debugger and run your app.
   Your breakpoint triggers for every SQL statement.
   Disable this breakpoint until your app is where you want to look at SQL.

There are more tracing options but to capture SQL the SQLPreview of Transaction (and of DataWindow + DataStore) are plenty.

HTH /Michael

Hi Michael

The string passed to the procedure contains N prefix as you see below

ls_sql = " update "+ls_table_name+" set "+ls_colname+" = N'"+ls+"' WHERE serial_number = "+String(ll_serial_number)+";"

On my machine it works with disablebind=0 even without NcharBind=1. It seems to default to 1. I will anyhow put it at customer Place tomorrow and see.

Regarding other errors due to disableBind=0, one immediate error is "The data type Char and Text are incompatible in the greater than or equal to operation" in a function. I need to find out exactly where.

Could you guide me exactly how to trace the exact SQL statement.

Thanks for your inputs so far.

Regards

Vinay

"DisableBind = 0 works!" => That's a hint on what is happening.

Sidebar :: DisableBind=1 means you request PowerBuilder to perform inlining of parameter values. Which is exactly where SQL Injection is possible.

I know, PowerBuilder is good at escaping potentially risky character sequences - but in any case: From "avoid SQL injection" approach you should consider using only DisableBind=0.

The inlining of variable values could have a deficiency of not inserting the >N< prefix on text strings to indicate string is Unicode (instead of ANSI).

Could you try to capture the explicit SQL statement sent to the database when you call the stored procedure? Either trace facility at SQL Server or on PB side - or Transaction object's SQLPreview?

I expect this happens:

// Text sent from PB app
sp_execute_sql '...some text...'

// Correct text to send (notice prefix)
sp_execute_sql N'...some text...'

I will do a test using PB 2019 R2 and MS SQL 2019 - stay tuned (unless someone beats me to the finish line with exact explanation of issue at hand.

HTH /Michael

Hi Roland/Michael

The procedure is as below

ALTER  procedure [dbo].[sp_execute_sql] @as_sql nvarchar (4000) AS IF @as_sql IS NOT NULL AND LTRIM(RTRIM(@as_sql)) <> '' EXEC (@as_sql)

Update statement in SQL Server works fine

Update cu168_riskfras_lang
set riskfras_beskrivning_backup = N'Υγρό και ατμοί πολύ εύφλεκτα.'

Select statement returns correct result.

Its just that update from powerbuilder code does not work. Tried putting disablebind=0 in the connectstring then it works on development PC but not on other and moreover it creates problems elsewhere.

Thanks

Vinay

Make sure the stored procedure source code declares the argument as nvarchar.

You probably have some character conversion going on.

PowerBuilder uses UTF-16. So does MSSQL nvarchar. However, it seems your string is sent as VARCHAR instead of NVARCHAR.

Try the following in DB Painter's ISQL (Notice the prefix = N on second select):

select 'varchar' [Type], 'Α α, Β β, Γ γ, Δ δ, Ε ε, Ζ ζ, Η η, Θ θ, Ι ι, Κ κ, Λ λ, Μ μ, Ν ν, Ξ ξ, Ο ο, Π π, Ρ ρ, Σ σ/ς, Τ τ, Υ υ, Φ φ, Χ χ, Ψ ψ, and Ω ω' [Text]
union
select 'nvarchar' [Type], N'Α α, Β β, Γ γ, Δ δ, Ε ε, Ζ ζ, Η η, Θ θ, Ι ι, Κ κ, Λ λ, Μ μ, Ν ν, Ξ ξ, Ο ο, Π π, Ρ ρ, Σ σ/ς, Τ τ, Υ υ, Φ φ, Χ χ, Ψ ψ, and Ω ω' [Text];

What I see in the Results pane is:

HTH /Michael