Syntax Issue With MSSQL T-SQL Statement in PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dickey
Forum Commoner
Posts: 50
Joined: Thu May 16, 2002 8:04 pm
Location: Sydney, Australia

Syntax Issue With MSSQL T-SQL Statement in PHP

Post by dickey »

Everah | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I am using MSSQL with high encryption and PHP

This executes fine in SQL Server Mgt Studio:

[syntax="sql"]INSERT INTO [PSS].[production].[pps_groups]
(rec_id, grp_name, member, amt)
VALUES
(
  EncryptByAsymKey(AsymKey_ID('asymmetric_key'),N'1'),
  EncryptByAsymKey(AsymKey_ID('asymmetric_key'),N'WINNERS'),
  EncryptByAsymKey(AsymKey_ID('asymmetric_key'),N'1000001'),
  EncryptByAsymKey(AsymKey_ID('asymmetric_key'),N'1000.00')
);
Note the use of N preceding the clear text value defines the clear text value as a Unicode literal. C++ programmers have a similar construct being `L`.

Using this syntax in a mssql query inserts null values in the db.

Code: Select all

$q = "INSERT INTO [PSS].[production].[pps_groups] 
(rec_id, grp_name, member, amt) 
VALUES(
EncryptByAsymKey(AsymKey_ID('asymmetric_partner'),N'1'),
EncryptByAsymKey(AsymKey_ID('asymmetric_partner'),N'testy'),
EncryptByAsymKey(AsymKey_ID('asymmetric_partner'),N'1000001'),
EncryptByAsymKey(AsymKey_ID('asymmetric_partner'),N'12222.99')
)";
The question being how do specify the clear text value as unicode, before encrypting it, and without the N representing a Null.


Any assistance greatly appreciated.

- Andrew


Everah | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Post Reply