[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

How to manage encyps queries mssql when sending conditioning values

we are working with encrpted sp and wanted to know EE advice when needing a store procedure to have a "where" clause in the querey.  Is it treated as regular SP?
0
rayluvs
Asked:
rayluvs
  • 5
  • 5
1 Solution
 
arnoldCommented:
Your encryption is database level or application level?
One you can use straight where since the db will decrypt and search the index, the other you will be looking for the encrypted hash pattern in the index. Though usually, application level encrypted columns are not searchable I.e passwords, personally identifying entries that should not be exposed/visible......
0
 
rayluvsAuthor Commented:
Query level (we set our SP with 'with encryption') but called from VB apps.  We have created a series of SP with encryption and works fine, but
we have come upon that certain queries needed to include conditions when executed from a VB apps.  For example SELECT * FROM TABLE WHERE SALES_CODE='VALUE-FROM-VB'; in vb

cmd.CommandText = "select SALES_CODE from TABLE where SALES_CODE='" & SalesCodeField.Value & "'"
Set rst = cmd.Execute

Open in new window


Our concern, how to handle that value being sent from VBA?
0
 
arnoldCommented:
Is encryption enabled on the DB in question?
If you merely encrypt the connection between the client and the SQL server, the where clause is fine since there is no encryption on the DB itself. I.e. You are securing the query from being captured/analyzed.

Possibly similar to those who host websites behind https, the data is only encrypted during transport. It is not encrypted on the server nor is it encrypted when displayed in the clients, browser.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
rayluvsAuthor Commented:
Sorry for the delay.

No DB encryption enabled in server, we created out SP with the clause or argument "with encryption".

So you're saying if we need to run an encrypted SP based on condition, use the  "where" clause?
0
 
arnoldCommented:
The encryption if I understand your response deals with securing the communication channel rather than the data.

Have you tried using the where clause and got an error no data while direct query using the same parameters returns info?
0
 
rayluvsAuthor Commented:
No we haven't.   That why we placed the question.  We thought that being encrypted the SP would not work with "where", that is, sending arguments to the SP to get a "resulting" response from the store procedure.
0
 
arnoldCommented:
IMHO, testing when there is no impact to the data is the best way to get an answer.

The only thing that is encrypted in this scenario is the SP iteself. i.e. its logic, process can not be analyzed from the SP listing.

so to your point, you can add where clause, etc. but you should use conditional/optional where clause to use the SP in more than one scenario.
0
 
rayluvsAuthor Commented:
We definitely are doing something wrong.  Below is the SP created.  We want to run it, send the value of the column we are looking for and have the result displayed back (or retrieved in our VBA)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE __testSP
(@Param1 char(15)
,@Param2 char(15) OUTPUT)
with encryption as BEGIN
	SET NOCOUNT ON;
	select Col1 from TableName
	WHERE Col1= @Param1
	SELECT @Param2 = @Param1
	RETURN 
END
GO

Open in new window


What are we doing wrong?

Note:
We are running it as "exec __testSP('pp1000')
0
 
arnoldCommented:
What is the point of assigning the @param1 you provide when calling the SP and assigning it to PARAM2?

Presumably you want the table which is the result of the select Col1 from TableName
      WHERE Col1= @Param1

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE __testSP
(@Param1 char(15)
,@Param2 char(15) OUTPUT)
with encryption as BEGIN
	SET NOCOUNT ON;
	select @Param1=Col1 from TableName
	WHERE Col1= @Param1
	RETURN 
END
GO

Open in new window

@variable=exec sp
@variable defined as table will get the resulting dataset.
0
 
rayluvsAuthor Commented:
Thanx!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now