Database Security

[LinuxKommy]

I'm writing a program that ultimately will dump data into our old DB server (COBOL) using a 3rd-party ODBC driver. I have absolutely no problem with the SQL statements, but the server requires that you log on EVERY time. it pops up a window titled "GRANT and REVOKE Security," and there is no way to bypass it by specifying the username and password in the connection string. One goofy way I considered is a vb script that waits five seconds, then uses SendKeys() to write the Username and password and hit enter. This works ok, but if you log in again soon after a query, it sometimes skips the password. I need a way to be sure that the window is actually present.

I thought about if there was a way to search through all open windows to look for that specific window title and then execute the script. Problem is, I'm not sure how to search the window titles, or if this is even a good idea. if anyone has other suggestions, i'd LOVE to hear them!

thanks,
DHG

 

[vb5prgrmr]

LinuxKommy have you read FAQ222-2244 item 15 yet? You should!

FindWindow API or EnumWindows API

Don't forget to read FAQ222-2244!

Good Luck

 

[koala15]

This is from the MSDN it may be of assistance:

HOWTO: Get a Window Handle Without Specifying an Exact Title
ID: Q147659



--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Visual Basic Control Creation, Learning, Professional, and Enterprise Editions for Windows, version 5.0
Microsoft Visual Basic Standard, Professional, and Enterprise Editions, 32-bit only, for Windows, version 4.0

--------------------------------------------------------------------------------


SUMMARY
The Visual Basic AppActivate command can only activate a window if you know the exact window title. Similarly, the Windows FindWindow function can only find a window handle if you know the exact window title.

This article demonstrates how to search for a window that has a title that is like the title you specify -- but not an exact match. The sample code searches through the available windows, comparing the window titles to a pattern by using the Visual Basic Like operator. You can also use the sample code to find a window based on its class name or ID. This can be extremely helpful when you need to send keystrokes to other Applications.



MORE INFORMATION

FindWindowLike Function
The sample code provides a find window function named FindWindowLike. FindWindowLike does a recursive search for windows matching the description you give it. After completing the search, FindWindow returns the number of windows it found that match your description. It also returns the window handles in an array that you pass to it. Once you have a window handle, you can call many Windows API functions to manipulate it. For example, you could set the focus to it, or move it. The example in this article shows how to set the focus.
Parameters Passed to FindWindowLike Function
The FindWindowLike function searches for windows based on the four parameters you pass it:


The hWndStart parameter specifies the handle of the window you want to search under. For example, if you gave the handle to a Visual Basic form, FindWindowLike would search through all the controls on that form. If you pass 0 for hWndStart, FindWindowLike searches through all available windows.


The WindowText parameter specifies the pattern used to compare window text. The Caption property of a Visual Basic Form is the same as the window text. The pattern is any string following the guidelines for the Visual Basic Like operator.


The ClassName parameter specifies a pattern used to compare a window class name. A window's class name identifies the type of window. You can find out a window's class name by using the utility SPY.EXE that comes with Microsoft Visual C/C++, or you can use the example given in the following article in the Microsoft Knowledge Base:
Q112649 HOWTO: Get a Window's Class Name and Other Attributes



FindWindowLike uses the Visual Basic Like operator when comparing class names.


The ID parameter specifies a specific child ID. This parameter is very useful for finding specific controls in a window. A window's ID can also be found by using SPY.EXE or the technique described in the following article in the Microsoft Knowledge Base:
Q112649 How to Get a Window's Class Name and Other Attributes
The ID parameter can be a decimal number or a hexadecimal string. If you use a hexadecimal string, prefix the hexadecimal number with &H. If you do not want to search for a specific ID, specify Null for the ID parameter.


FindWindowLike Examples and Their Results
Here are several example calls to FindWindowLike and the results it should return:


r = FindWindowLike(hWnds(), 0, "*", "*", Null)
Returns: All the available windows.


r = FindWindowLike(hWnds(), 0, "*Excel*", "*", Null)
Returns: All the windows with "Excel" in the window text.


r = FindWindowLike(hWnds(), 0, "Microsoft Excel*", "XLMAIN", Null)
Returns: All the windows having window text that begins with "Microsoft Excel" and that contains class name "XLMAIN"


r = FindWindowLike(hWnds(), Form1.Hwnd, "*", "*", "&HA1")
Returns: The child window of the Form1 window that has an ID of hexadecimal A1.


r = FindWindowLike(hWnds(), Form1.Hwnd, "*", "*", 2)
Returns: The child window of the Form1 window that has an ID of 2.


Uses for FindWindowLike


The FindWindowLike function can be especially powerful when used in a series of searches. For example, you can search once to find an open instance of the PIF Editor that comes with Microsoft Windows. Then once you have the handle to the PIF Editor window, you can search again for a specific control's window handle by using the PIF Editor's window handle and the ID of the control's window. Here is an example:
r = FindWindowLike(hWnds(), 0, "PIF Editor*", "Pif", Null)
' Assuming the previous returned at least one handle
r = FindWindowLike(hWnds(), hWnds(1), "*", "*", 103)
After you have the control's window handle, you can give it the focus and send it keystrokes. In addition, there are a lot of other things you can do once you have a window's handle.
Sample Code
The sample code listed in the example below uses several Windows API functions to accomplish what was described above. Here are the key ones including descriptions of their uses:


GetDeskTopWindow gets the handle to the DeskTop window when 0 (zero) is passed as the hWndStart, so you can use it as a starting point for the recursive search.


GetWindow gets a window handle based on a relationship to another window handle, so you can use it to get child windows and their siblings.


GetWindowText gets the text of a window for comparison to the passed WindowText.


GetClassName gets a window's class name, which you can compare to the passed ClassName.


GetWindowLong gets information about a window. The sample code uses it to get the ID number of a window for comparison with the passed ID.


Step-by-Step Example
Start a new Standard EXE project. Form1 is created by default.


Add three Text boxes (Text1, Text2, and Text3) to Form1.


Put the following code in the Form1 click event:

Private Sub Form_Click()
Static hWnds() As Variant
' Find window with title "Form1":
r = FindWindowLike(hWnds(), 0, "Form1", "*", Null)
If r = 1 Then
Debug.Print "Found "; Hex(hWnds(1))
' Find a child window of "Form1" with ID=2:
' Notice that the handle from the first search is used.
r = FindWindowLike(hWnds(), hWnds(1), "*", "*", "&H00000002")
If r = 1 Then
Debug.Print "Found child "; Hex(hWnds(1))
Debug.Print "Setting focus to child ..."
' Set the focus to the child window with ID=2:
hw = hWnds(1)
r = SetFocusAPI(hw)
ElseIf r > 1 Then
' This should not happen.
Debug.Print "Found more than one child ID=2"
Else
Debug.Print "Did not find child ID=2"
End If
ElseIf r > 1 Then
Debug.Print "Found "; r; " Windows"
End If
End Sub



From the Insert Menu select Module to add a new module to the project.


Put the following code in the new module:

Declare Function SetFocusAPI Lib "user32" Alias "SetForegroundWindow" _
(ByVal hwnd As Long) As Long
Declare Function GetWindow Lib "user32" (ByVal hwnd As Long, _
ByVal wCmd As Long) As Long
Declare Function GetDesktopWindow Lib "user32" () As Long
Declare Function GetWindowLW Lib "user32" Alias "GetWindowLongA" _
ByVal hwnd As Long, ByVal nIndex As Long) As Long
Declare Function GetParent Lib "user32" (ByVal hwnd As Long) As Long
Declare Function GetClassName Lib "user32" Alias "GetClassNameA" _
(ByVal hwnd As Long, ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long
Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) _
As Long

Public Const GWL_ID = (-12)
Public Const GW_HWNDNEXT = 2
Public Const GW_CHILD = 5
'FindWindowLike
' - Finds the window handles of the windows matching the specified
' parameters
'
'hwndArray()
' - An integer array used to return the window handles
'
'hWndStart
' - The handle of the window to search under.
' - The routine searches through all of this window's children and their
' children recursively.
' - If hWndStart = 0 then the routine searches through all windows.
'
'WindowText
' - The pattern used with the Like operator to compare window's text.
'
'ClassName
' - The pattern used with the Like operator to compare window's class
' name.
'
'ID
' - A child ID number used to identify a window.
' - Can be a decimal number or a hex string.
' - Prefix hex strings with "&H" or an error will occur.
' - To ignore the ID pass the Visual Basic Null function.
'
'Returns
' - The number of windows that matched the parameters.
' - Also returns the window handles in hWndArray()
'
'----------------------------------------------------------------------
'Remove this next line to use the strong-typed declarations
#Const WinVar = True
#If WinVar Then
Function FindWindowLike(hWndArray() As Variant, _
ByVal hWndStart As Variant, WindowText As String, _
Classname As String, _ ID) As Integer Dim hwnd
Dim r
Static level
Static iFound
#ElseIf Win32 Then
Function FindWindowLike(hWndArray() As Long, ByVal hWndStart As Long, _
WindowText As String, Classname As String, ID) As Long
Dim hwnd As Long
Dim r As Long
' Hold the level of recursion:
Static level As Long
' Hold the number of matching windows:
Static iFound As Long
#ElseIf Win16 Then
Function FindWindowLike(hWndArray() As Integer, _
ByVal hWndStart As Integer, WindowText As String, _
Classname As String, ID) As Integer
Dim hwnd As Integer
Dim r As Integer
' Hold the level of recursion:
Static level As Integer
Hold the number of matching windows:
Static iFound As Integer
#End If
Dim sWindowText As String
Dim sClassname As String
Dim sID
' Initialize if necessary:
If level = 0 Then
iFound = 0
ReDim hWndArray(0 To 0)
If hWndStart = 0 Then hWndStart = GetDesktopWindow()
End If
' Increase recursion counter:
level = level + 1
' Get first child window:
hwnd = GetWindow(hWndStart, GW_CHILD)
Do Until hwnd = 0
DoEvents ' Not necessary
' Search children by recursion:
r = FindWindowLike(hWndArray(), hwnd, WindowText, Classname, ID)
' Get the window text and class name:
sWindowText = Space(255)
r = GetWindowText(hwnd, sWindowText, 255)
sWindowText = Left(sWindowText, r)
sClassname = Space(255)
r = GetClassName(hwnd, sClassname, 255)
sClassname = Left(sClassname, r)
' If window is a child get the ID:
If GetParent(hwnd) <> 0 Then
r = GetWindowLW(hwnd, GWL_ID)
sID = CLng("&H" & Hex(r))
Else
sID = Null
End If
' Check that window matches the search parameters:
If sWindowText Like WindowText And sClassname Like Classname Then
If IsNull(ID) Then
' If find a match, increment counter and
' add handle to array:
iFound = iFound + 1
ReDim Preserve hWndArray(0 To iFound)
hWndArray(iFound) = hwnd
ElseIf Not IsNull(sID) Then
If CLng(sID) = CLng(ID) Then
' If find a match increment counter and
' add handle to array:
iFound = iFound + 1
ReDim Preserve hWndArray(0 To iFound)
hWndArray(iFound) = hwnd
End If
End If
Debug.Print "Window Found: "
Debug.Print " Window Text : " & sWindowText
Debug.Print " Window Class : " & sClassname
Debug.Print " Window Handle: " & CStr(hwnd)
End If
' Get next child window:
hwnd = GetWindow(hwnd, GW_HWNDNEXT)
Loop
' Decrement recursion counter:
level = level - 1
' Return the number of windows found:
FindWindowLike = iFound
End Function



Save the Project.


Run the code, and then click the form.


First, the program searches for Form1. If Form1 is found, the program searches Form1 for a control with an ID of 2. It should find one because Visual Basic numbers the IDs as you add controls. With three text boxes on Form1, one should have an ID of 2. After finding the control, the code sets the focus to the window by calling SetFocusAPI.

If you set the focus to one of your controls before clicking the Form, you should see the focus shift, always to the same control, when you click the form.


&quot;Life is full of learning, and then there is wisdom&quot;

 

[fredericofonseca]

Can you tell us who is the COBOL vendor, and what is the ODBC driver you are using (name, vendor and version)


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[LinuxKommy]

First, thank you for your replies.

Secondly, the database is called TechGAP, which i believe now belongs to integrasoft.

The ODBC driver is the Transoft U/SQL driver, v3.10.0.400.

koala15, thank you for the article...i will try it this morning, but from an initial read, it seems to be what i want.

vb5prgrmr, I have read that forum before and I wasn't aware that I was violating any of those rules....If i have, i am truly sorry. Which (if any) were you referring to, so that i don't reepeat that mistake?

thanks,
dhg

 

[fredericofonseca]

LinuxKommy,

I asked which ODBC it was, as I find it very weird that a username is asked everytime you try to do a query.

I would look through the documentation supplied by Transoft, (or even ask them directly), and look for way to bypass that.
It is possible that you have setup some parameters on the definition of the files being accessed that causes that window to pop-up.

I am trying to get my hands on that particular version and I'll have a go at it if/when I have it.

Just keep the email notification on for this thread.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[koala15]

Regarding the comment by fredericofonseca, yes it is weird.

I did a quick search of the web and found the following at

http://www.seihq.com/ts-ubl.html

regarding the Transoft U/SQL driver.

Plus ODBC access
U/SQL Server is Open Database Connectivity (ODBC) enabled and by simply adding Transoft's U/SQL Client product, you can use any PC client- based ODBC- aware software to access U/BL data as if it were a relational database.

This means you can report on your data using popular PC products, such as Crystal Reports, Impromptu or Microsoft Query. You can even develop client/server applications which both access and update your U/BL data using products like Visual Basic, Visual C++ or Powerbuilder.

----------------------------------------

Since it is supposed to be ODBC compliant, the application should be able to generate a connection (involving a username and password) and have that connection maintained until disconnection.

LinuxKommy, could you please post both the connection string and command as well as the first access command.


&quot;Life is full of learning, and then there is wisdom&quot;

 

[johnwm]

koala15,
Rather than violating Microsoft's copyright it's often better to simply post the link to the article. It also saves space!

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q147659

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[LinuxKommy]

I can't use a connection string b/c it never would work....however this is from the file DSN that i use.

[ODBC]
DRIVER=Transoft ODBC Driver
Description=wtrans
Timeout=35
Port=7000
Server=ppwsco
TSDSN=wtrans

I even tried adding lines for the username and password, but that didn't work. I can also show you an image of the login window, if that will help. just tell me how i can paste it here, or i can email it to you.

thanks again,
DHG

 

[fredericofonseca]

Can you please post a sample code of how you are
1- connection to the ODBC
2- retreiving some data
3- Updating some data





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[LinuxKommy]

here is my db sub:

Public Function DbOpenGAP() As Boolean
    On Error GoTo Err_dbConnect
    DbOpenGAP = False
   
    sConn = "DSN=PPC;"


Set GAPcn = New ADODB.Connection
    GAPcn.Open sConn
    DbOpenGAP = True
    Exit Function
Err_dbConnect:
    MsgBox Error
    MsgBox "Database did not connect properly"
    Exit Function
End Function

here is a sample sub:

Private Sub Command2_Click()
'test procedure
Call DbOpenGAP
With GAPrs
.Source = "SELECT * FROM DFBILL WHERE ProdCat=711"

Debug.Print .Source
Set .ActiveConnection = GAPcn
.CursorLocation = adUseClient
.Open , , adOpenDynamic, adLockOptimistic

Do Until .EOF
MsgBox ![Number]

.MoveNext
Loop

.Close
End With

End Sub

here is another SQL statement:
insert into oeline (company1,orderno,lineno,linecode,product) VALUES (4,818000,212,"S","3001")

if you need more info, let me know....thanks!

 

[fredericofonseca]

And how did you try to add the username/password to your sConn string?

And what was the result?


Also note that you don't need to open a new connection for each query you do.

Connections can be reused, and can have several queries running.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[LinuxKommy]

oh i know....that was just an old bit of code....i changed it to first look for the open connection, and use it if it is open.

i tried to do:

sConn = "DSN=PPC;USER=user;PASSWORD=pw;"

and also adding those lines to the file DSN (using notepad)

neither way worked.