CODEWITHSUNDEEP
c#.netsql-server
incomplete
incomplete

Reputation: 546

Incorrect syntax error

I need to insert encrypted credit card number into my table, i have to use symmetric key logic of sql server. When im trying to run the below code it gives error incorrect syntax near Processors_Key_0. I dont understand where im going wrong.

string query = "OPEN SYMMETRIC KEY Processors_Key_01 DECRYPTION BY CERTIFICATE Processors INSERT INTO [Payees] VALUES ("
                + objpayee.BookingID + ", '" + address.ID + ", " + objpayee.PaymentTypeID +
                ", " + objpayee.LName +", " + objpayee.FName + ", EncryptByKey(Key_GUID('Processors_Key_01'), '" + objpayee.CardNumber +
                "'), "+ objpayee.ExpMonth + "', " + objpayee.ExpYear +", '" + objpayee.CardNumber.Substring(objpayee.CardNumber.Length - 4) +
                "', " + objpayee.Email + "', " + objpayee.Phone + "',null,null,null,null,'1-1-2015',null,null,'1-1-2015') CLOSE SYMMETRIC KEY Processors_Key_01; SELECT SCOPE_IDENTITY();";
        var id = (int)_db.ExecuteStoreQuery<decimal>(query).SingleOrDefault();

Upvotes: 2

Views: 469

Answers (4)

Shailesh
Shailesh

Reputation: 1218

You can resolve the problem by using

INSERT INTO [PAYEES] SELECT Column1, Column2....ColumnX.

I am bit worried here. Why you are trying to store credit card information in your database, it is against PCI security standards. All the credit card information should be stored at payment providers database. Please refer https://www.pcisecuritystandards.org/ for more details.

Upvotes: 0

Tim M.
Tim M.

Reputation: 54397

This might be an error: ", '" + address.ID + ", " (note the opening single quote but no closing quote).

Use a parameterized query and your code will be significantly cleaner (not to mention safer). With such a large statement it's easy to create syntax errors.

Also note that unless your environment is fully PCI compliant from beginning to end, you absolutely should not be storing credit card information, regardless of encryption.

More PIC compliance/audit info from Visa.

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1503290

Well, if you print out your SQL statement you'll probably find the problem. However, you shouldn't just fix your syntax. You should ditch the way you're using value entirely.

You should absolutely use parameterized SQL here:

  • It'll make your SQL a lot easier to read
  • You won't need to worry about adding ' to the start and end of each value
  • It'll protect you from SQL injection attacks

Putting values directly into the SQL is a very, very bad idea, and you just shouldn't do it.

Look at the docs for SqlCommand.Parameters for an example of how to use parameterized SQL. Alternatively, use an ORM or something similar which will hide all of this from you in the first place...

Upvotes: 4

juergen d
juergen d

Reputation: 204894

Instead of

... INSERT INTO [Payees] VALUES (...)

do this

... INSERT INTO [Payees] ...

Upvotes: 1

Related Questions