Pre-release Multi-Tenant Connection String

[kkfin]

Re: Pre-release Multi-Tenant Connection String

Is this thread still valid?

There is new pre release. I wait your reports of it:)

[alexc]

Re: Pre-release Multi-Tenant Connection String

Its valid if you test the feature and it works

[alexc]

Re: Pre-release Multi-Tenant Connection String

David i have tried breaking it and it simply works so not sure what you are doing different. Can someone else test this feature.

The {if} in SQL Query was in the release prior so don't think is related and does not seem to affect it anything. Must be an expression not fully evaluating when displaying in the alpha developer

The {lf} are purely cosmetic. alpha now preserve line breaks when editing field properties. you will notice that when you click in the sql statement field, it shows as a multi-line item.

[Davidk]

Re: Pre-release Multi-Tenant Connection String

Using today's pre-release, full install, new UX... I'm getting the same bad results.

Alex... going back to a previous point... if I go to my AlphaDAOConnection strings, and edit one of my Tenant Connections, an "Edit Alpha DAO Connection" dialog will open. If I click on the "Build" button a "Select an API" dialog opens. From this point I must re-built my connection string. Is this not happening for you?

If I then select "MultiTenant" and fill in the connection string properties and then click the "Test Connection" button... nothing happens. If can click OK and then click the "Test connection" link and that will test the connection.

Are you not seeing any of this?

[alexc]

Re: Pre-release Multi-Tenant Connection String

Using today's pre-release, full install, new UX... I'm getting the same bad results.

Alex... going back to a previous point... if I go to my AlphaDAOConnection strings, and edit one of my Tenant Connections, an "Edit Alpha DAO Connection" dialog will open. If I click on the "Build" button a "Select an API" dialog opens. From this point I must re-built my connection string. Is this not happening for you?

If I then select "MultiTenant" and fill in the connection string properties and then click the "Test Connection" button... nothing happens. If can click OK and then click the "Test connection" link and that will test the connection.

Are you not seeing any of this?

When i click the build button to edit it goes straight into the connection properties for multi-tenant. I have noticed the Test Connection button does not work as you mentioned.

[Davidk]

Re: Pre-release Multi-Tenant Connection String

Thanks Alex... appreciated.

In build 5221, my AlphaDAO connection strings open properly when edited. In yesterdays and todays pre-release existing connection strings are not recognized and go through a new build process when edited. Even connection strings I've had for years no longer edit.

Something in these pre-releases is quite broken for me.

[Davidk]

Re: Pre-release Multi-Tenant Connection String

I put in a new install of 5221. I can edit AlphaDAO connection strings. Then I updated that install with build 5417. I can no longer edit AlphaDAO Connection Strings.

So... beware 5417.

[Davidk]

Re: Pre-release Multi-Tenant Connection String

For anyone wanting to use a Character based TenantId for a Multi-Tenant Connection String, here is the bug fix you need to implement.

Go through the process of creating a Mutli-Tenant Connection string, as shown in Selwyn's video, https://www.youtube.com/watch?v=IM3ul66RcGQ
You must select XbasicCustom as the ConnectionType in order to have the XBasic class built for you. Then you can correct the code.

Once the XBasic Class is created, edit the function named "query". Around line 166 you'll see this code... which is correct code...

			if left(TenantIDFieldType,1) = "C" then 
				_tenantId = stritran(_tenantId,"'","''")
				dim whereStatement as c = _tenantidfieldname  + " = '" +_tenantId + "'"
			else
				dim whereStatement as c = _tenantidfieldname  + " = " +_tenantId
			end if

Here Alpha is checking for the TenantId field type. If it's "C" (character) then they are wrapping the value of _tenantId in quotes. _tenantId is the tenant session variable. All good.

Right after this section of code is this...

			if se.where.SQLStatement <> "" then
				whereStatement = se.where.SQLStatement + " and "+_tenantidfieldname+" = "+_tenantId
			end if

If there is already a "where" statement in place (one that you have added to your List as a filter) then append the additional tenant whereStatement to it. However, note the missing field type test and, if Character, the missing quotes.
This is why the Multi-tenant sql statement fails if the tenandId is Character and you have additional where statements.

To fix this, I commented out the code which tests for a where statement and added code to the section where the TenantIDFieldTType is tested. This code works...

			if left(TenantIDFieldType,1) = "C" then 
				_tenantId = stritran(_tenantId,"'","''")
				dim whereStatement as c = _tenantidfieldname  + " = '" +_tenantId + "'"
				if se.where.SQLStatement <> "" then
					whereStatement = se.where.SQLStatement + " and "+_tenantidfieldname+" = '" +_tenantId + "'"
				end if			
			else
				dim whereStatement as c = _tenantidfieldname  + " = " +_tenantId
				if se.where.SQLStatement <> "" then
					whereStatement = se.where.SQLStatement + " and "+_tenantidfieldname+" = "+_tenantId
				end if				
			end if

The Multi-tenant feature wasn't tested for a Character Type tenantId. Why it worked for Alex is a complete mystery to me.

[RichCPT]

Re: Pre-release Multi-Tenant Connection String

1) Appending an expression like that to the existing "WHERE" clause is not safe; you may get unintended results. It is much safer to wrap both expressions in parentheses. Or, in this case we know the second expression is a simple one: fieldx = valuey. So, we just need to wrap the existing WHERE clause in parenthesis before appending the tenant id expression. Run the following example in a query window that I found here: https://stackoverflow.com/questions/...nce-and-and-or

Declare @x tinyInt = 1
Declare @y tinyInt = 0
Declare @z tinyInt = 0
Select Case When @x=1 OR @y=1 And @z=1 Then 'T' Else 'F' End -- outputs T
Select Case When (@x=1 OR @y=1) And @z=1 Then 'T' Else 'F' End -- outputs F

We hope that both select statements will output the same result, but they won't. We need those parenthesis to get what we want. So, I hope that Alpha knows to put those parentheses in when they combine base filters with users filters elsewhere in the system and that alpha knows that both expression are going to need surrounding parenthesis.

2) To guard against SQL injection a SQL::Argument must be used there. Sure, it is supposed to be difficult for hacker to change the value of a session variable by altering code on the client-side. But I suspect it really isn't that hard to alter a session variable maliciously.

3) I use a GUID as a tenant ID. I hope Alpha tests those out as well. Alpha may have to know to set the argument type to "K" when adding them into a SQL::Arguments object (assuming they fix the demonstrated code to use arguments).

[kkfin]

Re: Pre-release Multi-Tenant Connection String

But I suspect it really isn't that hard to alter a session variable maliciously.

It has to be very hard otherwise the hole concept is broken. You can change cookie but not session variable. If some one can change session variable it means that your server has been exploited.