4

We're trying to create a asp.net page that gives our users the ability to pull information directly from their own database to our website. The user will have the ability to provide:

  • hostname, port, database name, username, password, and query.

I have some serious security concerns regarding this and was wondering how this page could be secured so that we're preventing users from pointing to the localhost database or other type hacks that could enable them to have access to our database. Can anyone please advise?

We're using SqlConnectionStringBuilder to build the connection to the user's database and doing some simple checks to ensure that the host cannot be "localhost" or other addresses that point to our server. I feel like doing this leaves a potential security holes open.

Also, the query that they provide is checked against some keywords that shouldn't be allowed. Again, I think this leaves a lot open if not properly implemented. (We essentially want them to only be able to do a SELECT from their own DB).

Finally, we do an EXEC sp_executesql with the query of the user.

I'd love to hear how others have dealt with this? Klipfolio is an organization that has a similar type functionality so if anyone knows how they've addressed this issue, that would be really awesome!!

Thanks!

Improve this question
3
  • You mention that users will give you a username/password combination. This is tagged "sql-server". Are you using SQL Server security, or integrated security (i.e. username/password represents a Windows user)? Overall, this feels like you are writing an open SQL Injection tool. However, you are at least using the user's credentials. Consider allowing users to pick databases, tables, join tables and fields from a list and then composing the queries from the info they give you, rather than blindly executing SQL they give you. Commented Sep 23, 2018 at 23:05
  • The user should be pointing to their own sql server and providing us with credentials to access it. So we'll use SQL security, not integrated security. If I can ensure the user can't point the sql server back to us, then I have a lot less concerns. Unless this can be secured, I totally agree, this is basically an open SQL Injection bomb waiting to go off.... We can't limit the entries to a list. Commented Sep 24, 2018 at 12:53
  • I was more thinking that you have a whitelisted list of servers. They pick one. You query and get a list of databases. They pick one. They you write a simple sql builder, you present them with a list of tables. They pick tables to select from, to join to, etc. The idea being that you restrict them to Select statements from whitelisted machines and non-system tables. Commented Sep 24, 2018 at 14:31

2 Answers 2

Reset to default
1

Without knowing all the particulars of your situation I don't think your approach is necessarily the best. "Normally" your database server is not exposed to the world, it is behind a firewall and direct connections from the outside world are not allowed. The people that will be using your webpage are likely to also have their database behind a firewall and thus even if were not trying to do anything malicious your webserver will not be able to make a direct connection to their database server because it is likely behind its own firewall. They would have to either expose it to the world or know the IP address of your server to poke a hole in their firewall to allow your server to connect.

Whenever I've allowed clients to upload data to my server it is been via a text or csv file or an Excel file. This allows you to get around any firewall issues on the client side. Now you need to worry about SQL Injection attacks within the data. So there are two things to do: first make sure you use parameters when performing an INSERT or UPDATE, and the second is to make sure the process that is performing the upload has the lowest possible privileges to your database.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

In this case, the user will have to whitelist our IP in their firewall and possibly add an SQL user their DB (similar to klipfolio). The concern I have is that we're creating a webpage that asks for their credentials and query. If somehow they decide to say that their ip address is the same as our (localhost, or just using our domain address), my concern is that they could easily start running queries on our DB instead of theirs, bypassing firewall and other security measures. The webpage is configured to connect to our DB via integrated security so that could give them quite a lot of access
1

If you must make a direct connection to the clients' database then I would do the following things to enhance security.

First is resolve the hostname to ip addresses.

IPAddress[] addresslist = Dns.GetHostAddresses(hostname);

Then check the results in the addresslist that they do not resolve to any private addresses as these would not work to connect to a client in any case.

10.0.0.0 to 10.255.255.255. 172.16.0.0 to 172.31.255.255. 192.168.0.0 to 192.168.255.255.

Also make sure that the address does not resolve to your own public address(es).

The second thing is at least just for this one function do not use a Trusted Connection. Instead use a username/password and assign that user's security rights on the database to be the minimum possible to accomplish the task of uploading data. And still use parameterized calls when importing the data.

Improve this answer

4 Comments

I like where this is going. We won't allow trusted conn's so user has to provide a user with their own Username/PW. Is there a way to programmatically obtain all the ip's associated to our domain without the risk of forgetting to add an IP in the future(or someone adds an additional DNS entry and it's not handled here)? I'm not sure how we can use parameterized calls in this case. The connection string is built using SqlConnectionStringBuilder which should provide some level of protection. The user provides us with the query which we execute using "EXECUTE sp_executesql N'" + query +"'"
I don't so much mind what the query is or what they try to do with(as long as we handle for sql or javascript injection). My main concern is that a user can't trick the system into running their queries against the server hosting the webpage instead of their own database. I feel like all these methods require us to really ensure we've covered every single possible scenario and perhaps it's just a matter of time before a clever hacker determines a scenario that isn't handled by us.
disregard my question about getting all the ip's to a domain. You answered it in your answer :-0
The parameterized query is for your server not theirs. When you import the data some of the data might contain a sql statement so when you import it you use a parameterized insert statement. I'm not sure how you are doing your inserts and how you are doing your column mapping nor am I even sure you are doing this in code or purely though SQL statements where you insert into your database based on a select from their database. It all depends.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.