ASP Classic Webapp - 通过登录连接到不同的SQL DB

Need a way to connect to a unique SQL db via login in ASP classic.

需要一种方法通过ASP经典登录连接到唯一的SQL数据库。

THE SETUP

• Webapp: ASP classic/SQL 2005.

Webapp:ASP classic / SQL 2005。

• Webapp stores information for multiple companies.

Webapp存储多家公司的信息。

• All data stored in one master SQL 2005. All db's will be on same server.

所有数据都存储在一个主SQL 2005中。所有数据库都将位于同一服务器上。

• Each user has a unique login (Company, User ID, Password)

每个用户都有一个唯一的登录名(公司,用户ID,密码)

• Connection is with master db include file using DSN-less connection

使用无DSN连接与主数据库包含文件连接

• IE: (dbConn.Open "driver={SQL Server};server=11.22.333.444;database=mywebdb","mylogin","mypassword")

IE:(dbConn.Open“driver = {SQL Server}; server = 11.22.333.444; database = mywebdb”,“mylogin”,“mypassword”)

THE NEED:

Want to split companies into their own database. When the user logins, the company name will tell the APP to use a unique SQL db connection for each company.

想要将公司分成自己的数据库。当用户登录时,公司名称将告诉APP为每个公司使用唯一的SQL数据库连接。

Since dynamic include files are not an option, what is best route to go?

由于动态包含文件不是一个选项,最佳路径是什么?

Thank you!

2 个解决方案

#1

The connection string must be stored some where right? A include .asp I guess.

连接字符串必须存放在哪里对吗?我猜是包括.asp。

Add code in that include to examine the company name (stored in the session?) and fixup the connection string accordingly.

添加包含的代码以检查公司名称(存储在会话中?)并相应地修复连接字符串。

Edit:

The issue is you may have code out there in a myriad different ASP pages that assumes the appropriate connection string is available in a variable declared in you db.asp include file (lets call it m_connStr). You don't want to have to modify all these pages in order to meet this new requirement.

问题是你可能在无数不同的ASP页面中有代码,假设在db.asp包含文件中声明的变量中有适当的连接字符串(让我们称之为m_connStr)。您不希望必须修改所有这些页面才能满足此新要求。

Thus you only want to edit the db.asp include file and you just want m_connStr to magically point at the correct DB.

因此,您只想编辑db.asp包含文件,并且只需要m_connStr神奇地指向正确的数据库。

Have your logon page once you know the company set the database name in a Session variable.

知道公司在Session变量中设置数据库名称后,请准备好您的登录页面。

Your existing code has the connection string like this:-

您现有的代码具有如下连接字符串: -

m_connStr = "driver={SQL Server};server=11.22.333.444 database=mywebdb", "mylogin", "mypassword"

So we'll use a template:-

所以我们将使用一个模板: -

m_connStrTemplate = "driver={SQL Server};server=11.22.333.444 database=%db%", "mylogin", "mypassword"

If Session("database") <> "" Then
    m_connStr = Replace(m_connStrTemplate, "%db%", Session("database"))
End If

Note a non-existant database session variable causes the connection string to not be defined hence you can't accidentally connect to a default database.

请注意,不存在的数据库会话变量会导致无法定义连接字符串,因此您不会意外连接到默认数据库。

Now as far as all your ASP pages are concerned it's business as usual but the connection string will vary by session according to the company associated with the logged on user.

现在,就您的所有ASP页面而言,它是正常的业务,但连接字符串将根据与登录用户关联的公司的会话而有所不同。

#2

However you do it, you'll end up varying your connection string based on user input. Don't use user input directly, but validate it against a list of acceptable values. I suggest a Select Case statement to do this:

无论如何,您最终会根据用户输入改变连接字符串。不要直接使用用户输入,而是根据可接受的值列表对其进行验证。我建议使用Select Case语句来执行此操作:

' Do this when logging in: '
Dim companyName
companyName = Request.Form("companyName")

Select Case companyName
Case "company1"
    Session("companyDB") = "company1"
Case "company2"
    Session("companyDB") = "company2"
Case Else
    Session.Contents.Remove("companyDB")
    ' Invalid login! '
End Select

' Do this when connecting to the database: '
Dim connectionString
If Session("companyDB") Then
    connectionString = "...database=" & Session("companyDB") & "..."
Else
    '  Invalid login, go log in again '
End If

Keep in mind that this will lead to trouble if you have users who will want to open one company in one tab and another company in another tab. They are going to wonder why they can only see information for the company they logged into most recently.

请记住,如果您的用户想要在一个选项卡中打开一个公司而另一个公司在另一个选项卡中打开,则会导致问题。他们会想知道为什么他们只能看到他们最近登录的公司的信息。

If this is going to be an issue, you will probably want to pass a token around in the query string on each link. This adds complexity, but not terribly much (aside from the tedious task of changing every link). It would then look like this:

如果这将成为一个问题,您可能希望在每个链接上的查询字符串中传递令牌。这增加了复杂性,但并不是非常多(除了改变每个链接的繁琐任务)。它看起来像这样:

' Do this when logging in: '
Dim companyName
companyName = Request.Form("companyName")

Select Case companyName
Case "company1"
    Session("company1 - db") = "company1DBName"
Case "company2"
    Session("company2 - db") = "company2DBName"
Case Else
    ' Invalid login! '
End Select


' Do this when connecting to the database: '
Dim connectionString, companyToken
companyToken = Request("companyToken")
If Session(companyToken & " - db") Then
    connectionString = "...database=" & Session(companyToken & " - db") & "..."
Else
    '  Invalid login, go log in again
End If

This assumes that the token will be the same as the company name, for simplicity. So, for instance, somebody will log in for "company1." Having done so successfully, they get a session variable called "company1 - db", which contains the name of the database (in this case, "company1DBName").

为简单起见,这假定令牌与公司名称相同。因此,例如,有人会登录“company1”。成功完成后,他们会得到一个名为“company1-db”的会话变量,其中包含数据库的名称(在本例中为“company1DBName”)。

Now, every link they follow should have a query string, like "?companyToken=company1" So, when you are connecting to the database, you take that token and use it to find the right database name: Session("company1" + " - db") = "company1DBName"

现在,他们所遵循的每个链接都应该有一个查询字符串,例如“?companyToken = company1”。因此,当您连接到数据库时,您将获取该令牌并使用它来查找正确的数据库名称:Session(“company1”+“ - db“)=”company1DBName“

If they haven't logged in to that company yet (or if they just make up a company name), they won't have that session variable, and they have to go to the log in screen.

如果他们尚未登录该公司(或者他们只是组成公司名称),他们将没有该会话变量,他们必须进入登录屏幕。

If they log in under two companies at once, you can now handle it because you'll be obtaining the database name on every link.

如果他们一次登录两家公司,您现在可以处理它,因为您将在每个链接上获取数据库名称。

Make sense?

Whatever you do, do not use the user input to create the connection string directly. In other words, the following is the wrong way:

无论您做什么,都不要使用用户输入直接创建连接字符串。换句话说,以下是错误的方法:

Dim connectionString
connectionString = "...database=" & Request.Form("companyDB") & "..."

Good luck!