SQLCA connection

James Pendarvis Accepted Answer Pending Moderation

Wednesday, 18 December 2019 11:56 AM UTC
PowerBuilder
# 1

I have modified my code to include the function, it is still stripping the '.

I have added the character ~ to the string formation:

SQLCA.DBMS = profilestring(gs_inidbloc, 'DBMS_PROFILES', 'DBMS', '')
SQLCA.AutoCommit = False
SQLCA.Database = ls_database
SQLCA.DBParm = "DataSource= ~' "            + ls_source + "~'" &
                       + ",TrustedConnection=" + profilestring(gs_inidbloc, 'DBMS_PROFILES', 'TrustedConnection', '') &
                       + ",Namespace= "            + wf_quoted(ls_namespace, "'") &
                       + ",CommandTimeout= '1200' "

I did make the function global but here is its code:

if Left(as_text, 1) + Right(as_text, 2) = as_quote + as_quote then
return as_text
else
return as_quote + as_text + as_quote
end if

Connection string results

dbparm=DataSource=server\instance, TrustedConnection=1,Namespace='System.data.SqlClient',Database=db_version

Error is DBMS ADO.NET is not supported in your current installation.

I think if I can get the ' wrapped around the server\instance I might have a different result.

Comment

Michael Kramer
Wednesday, 18 December 2019 17:26 PM UTC

I would drop the tilde character since you are embedding single quotes in a double-quoted string. Hence, no need for the tilde characters.

BTW: I see your NameSpace segment is single-quoted (that's where you call your wf_quoted function.

What happens if you remove 2 x [ ~' ] around ls_datasource and instead call wf_quoted same as for NameSpace?

Helpful 0

James Pendarvis
Wednesday, 18 December 2019 19:00 PM UTC

It still drops the ' around DataSource and Database. It added the ' mark around the namespace regardless of the function being called.

I have a desktop application that uses ADO.NET. When I connection with a standard connection string:

Data Source = server\instance; Initial Catalog= database_name; Integrated Security = true I am successful in logging in.

Helpful 0

There are no comments made yet.

Michael Kramer Accepted Answer Pending Moderation

Monday, 16 December 2019 14:09 PM UTC
PowerBuilder
# 2

Sample function that applies quotes only when quotes are missing

// string function of_GetQuoted(readonly string as_text, readonly string as_quote)
if Left(as_text, 1) + Right(as_text, 2) = as_quote + as_quote then
   return as_text
else
   return as_quote + as_text + as_quote
end if

This function is easily inserted into existing expressions:

SQLCA.DBParm = "DataSource='" + ProfileString(gs_inidbloc, ...) + &

SQLCA.DBParm = "DataSource=" + of_Quoted(ProfileString(gs_inidbloc, ...), "'") + &

Comment

There are no comments made yet.

Michael Kramer Accepted Answer Pending Moderation

Monday, 16 December 2019 13:19 PM UTC
PowerBuilder
# 3

As long as your DataSource string contains no "dangerous" characters they perform equivalent. Examples of "dangerous" characters: Comma, semicolon, space, quotes, etc.

SQLCA.DBParm="DataSource='Alfa,DisableBind=1',TrustedConnection=1,..."

SQLCA.DBParm="DataSource=Alfa,DisableBind=1,TrustedConnection=1,..."

In most cases your data source name is safe - but who knows what an attack on your INI file may do to your DBParm text?

I personally would always use quotes to eliminate a risk I don't need.

HTH /Michael

Comment

James Pendarvis
Monday, 16 December 2019 13:26 PM UTC

Would charaters like _ or / be considered "dangerous". How can I force the tick marks?

Helpful 0

Michael Kramer
Monday, 16 December 2019 14:01 PM UTC

One options >>> (extra spaces for reading)

ls_datasource = ProfileString( . . . )

IF Left(ls_datasource, 1) + Right(ls_datasource, 1) <> " ' ' " THEN ls_datasource = " ' " + ls_datasource + " ' "

That one-liner colud be wrapper in a function to return a guaranteed quoted string. (see separate reply for code example)

Helpful 0

There are no comments made yet.

Resolved SQLCA connection