Ensuring functions within a webservice are secure in delphi

I am in the process of designing a server side webservice dll which will be accessible by developers from other companies to access data on a central server. the issues i need to consider are - ensuring they only see the data which they are allowed to see - allowing them to upload/ download files. again only ones they are allowed to.

I can devise a sql query which will contain the data which they will be able to see but i need to make sure that they can't edit the query to access other data. Also i will need to ensure that the upload, download functions are secure enough that they can't download other peoples files. Will it be enough to place the function procedures and objects I don't want them to see in the private declarations or will i need to do a bit more to control 开发者_C百科access.

I am also looking at controlling the webservice by password control. what is the best means of doing this within a webservice.

In a webservice you have total control over the functions that you expose to the outside world. Security will have to be implemented by yourself. So if a user requests a file to download, you will have to do a check and based on the rights of the user decide if it is OK or not.

Use queries with parameters to make sure nobody can mess with your SQL code. Query properteis should not be exposed over the service and there is no need to do so.

-- Edit:

I forgot to ask: are you thinking about a SOAP service or WebSnap?

Your best and most secure option is to not allow SQL to be entered directly, but rather to be built from methods you expose to the client. Whenever you process any string data, check for injection to make sure the user is not attempting to bypass what controls you have in place. The end consumer should never invoke a simple SQL statement directly.

For example, using a table "Products" and exposing this to the customer for consumption, the methods might look like the following:

function GetAllProductsByAmount(topn:integer):tDataset;
  //Returns all products sorted by amount, no more than topn.}
function GetAllProductsByName(topn:integer):tDataset;
  //Returns all products sorted by name, no more than topn}
function FindProductByName(name:string):tDataset;
  //returns all products which start with name}

The FindProductByName function would have a check to insure that there are no extra quotes in the name, and if so to return an empty set. I also generally perform an automatic wildcard query if the first query results in no data.

For the download/upload logic, make sure that the entire request goes through your system. The storage point for the user files should be in a protected area that is NOT exposed to the outside world, but is only available to the inside. When a request for a protected resource comes in, you can check the credentials and if they are not correct refuse the file. If everything is ok, then you open and stream the file to the user. If your files are extremely large, then consider an FTP server with individual users so that each user has its own private directory. This does require more maintenance, but would allow users to download their data faster since the FTP server is tuned for just this kind of access. As noted in the comments below, FTP is not secure enough since the username and passwords are sent plain text.

For this type of application what I have traditionally done was create a session cookie that contains a guid on the server and marked that in a table along with the last valid access date/time (and IP Address). The only time the session cookie gets created is upon successful login. Each time I get a request, I verify that this cookie is available and still valid (I time mine out after say 30 minutes), I then update the time stamp of the last access. If a user logs off then I also delete their session cookie. Any request with an invalid session cookie redirects to the login page. This keeps things simple, and reduces the amount of data that needs to be looked up to just who is online in the last 30 minutes (or not logged out and not yet cleaned up). I automatically purge all invalid logins which have expired at each login/logout attempt.

For your webservice, you can do something simular. Have a LOGIN routine which returns a guid and require the guid to be passed at each request. If its not valid, then reject the request.

Having it place a SQL query is inherently insecure since, as you pointed out, the client could find a way to mess with it. A better idea would be to place the query inside a stored procedure, and then allow the client to call the stored procedure. Make two of the parameters to the procedure a username and password, or something along those lines, that you can use to identify the user and decide what he's allowed to see.