rwallacej
asked on
Web Service to talk to SQL server database using custom queries
Hi
I have client with SQL server database on their server. They don't want to open the SQL server database to "world" so I thought that I could talk to it I could have say a web service? (previously used VPN but that is no longer)
I don't want client to have all the queries I use (not for any "bad" reason, rather just that we've taken a long time to develop).
So, on client side a service with method say something like
function GetData(theQuery as string)
do database stuff
execute theQuery
return result
end function
The problem with this might be SQL injection with variable theQuery ? how to prevent?
I know could use stored procedures, but to do that again the client would know all the queries I use
Please advise
I have client with SQL server database on their server. They don't want to open the SQL server database to "world" so I thought that I could talk to it I could have say a web service? (previously used VPN but that is no longer)
I don't want client to have all the queries I use (not for any "bad" reason, rather just that we've taken a long time to develop).
So, on client side a service with method say something like
function GetData(theQuery as string)
do database stuff
execute theQuery
return result
end function
The problem with this might be SQL injection with variable theQuery ? how to prevent?
I know could use stored procedures, but to do that again the client would know all the queries I use
Please advise
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In that case you would be safer to pass a parameterised query to your method, along with a collection of values, or name/value pairs.
So you could have a query like "select some_field(s) from some_table where some_field = ?", and then pass a value to your method as well.
So you could have a query like "select some_field(s) from some_table where some_field = ?", and then pass a value to your method as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Further to my previous post, from a security point of view:
When setting up an HTTP endpoint, you will need to decide between Basic, Digest, Integrated (NTLM, Kerberos), and SQL Authentication
A connection firstly authenticates at the HTTP transport level. If successful, the user's SID is used to authenticate with SQL. The exception is SQL Auth. The SQL Auth credentials are sent as part of the SOAP packet using WsSecurity Username token headers. One can also restrict access to only specified IPs or ranges of IPs. Even if a stored procedure is mapped, it can only be executed if the user has CONNECT permissions on the endpoint as well as EXECUTE permissions on the stored procedure.
When an endpoint is created, only members of the sysadmin role and the owner of the endpoint can connect to the endpoint. You must grant connect permission for users to access your endpoint; this is accomplished by executing the following statement:
GRANT CONNECT ON HTTP ENDPOINT::Employees_Select
To restrict access to an endpoint, the administrator can deny permission to the EVERYONE group, using the DENY CONNECT statement. Then, he can grant permission to specific individuals or roles, using the GRANT CONNECT statement.
When setting up an HTTP endpoint, you will need to decide between Basic, Digest, Integrated (NTLM, Kerberos), and SQL Authentication
A connection firstly authenticates at the HTTP transport level. If successful, the user's SID is used to authenticate with SQL. The exception is SQL Auth. The SQL Auth credentials are sent as part of the SOAP packet using WsSecurity Username token headers. One can also restrict access to only specified IPs or ranges of IPs. Even if a stored procedure is mapped, it can only be executed if the user has CONNECT permissions on the endpoint as well as EXECUTE permissions on the stored procedure.
When an endpoint is created, only members of the sysadmin role and the owner of the endpoint can connect to the endpoint. You must grant connect permission for users to access your endpoint; this is accomplished by executing the following statement:
GRANT CONNECT ON HTTP ENDPOINT::Employees_Select
To restrict access to an endpoint, the administrator can deny permission to the EVERYONE group, using the DENY CONNECT statement. Then, he can grant permission to specific individuals or roles, using the GRANT CONNECT statement.
Also HTTP endpoints can use SSL certificates to encrypt the communication going back and forth
ASKER
thanks for help
ASKER
however, I don't think I'd be able to do this, the client is "owner" of database (although I have written queries to do the smart stuff), I don't think they'd let me add stored procedures to it