autoit and using / connecting SQL DB

[FMS]

Hello,

I've made a script where this is a piece of, this because it will be a lit easyer to read and use for others iff solved.
I am willing to learn the use of SQL / autoit connection but can't find anythin helpfull on the internet
Or iff I find something it is a little dated.
The following script is also build whit dated material.

What mine consernens are is SQL injection and all the other security isseu's.
I hope somebody can look at mine script and help me whit making this work.

- Is this a good way to make a connection and check it or is there a better way?

At this point I've an error :
 

"D:\map\file.au3" (72) : ==> The requested action with this object has failed.:
$adCN.Open ($constrim)
$adCN^ ERROR
>Exit code: 1    Time: 18.51

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

$Form1 = GUICreate("Form1", 480, 100, 190, 320)
$button1 = GUICtrlCreateButton("check and run", 128, 24, 113, 33)
$Input_ip = GUICtrlCreateInput("127.0.0.1", 8, 16, 113, 21)
$Input_usr = GUICtrlCreateInput("harry", 8, 40, 113, 21)
$connection_label = GUICtrlCreateLabel("connection = ", 248, 16, 52, 17)
$user_label = GUICtrlCreateLabel("username = ", 248, 40, 52, 17)
$connection_status_label = GUICtrlCreateLabel("connection not checked", 304, 16, 190, 70)
$user_status_label = GUICtrlCreateLabel("user not checked", 304, 40, 190, 17)

GUISetState(@SW_SHOW)

While 1
   $nMsg = GUIGetMsg()
   Switch $nMsg
   Case $GUI_EVENT_CLOSE
      Exit
   Case $button1
      $checked = check_connection_usr(GUICtrlRead($Input_ip),GUICtrlRead($Input_usr))
      if $checked Then
         ;MsgBox($MB_SYSTEMMODAL, "function","true")
         get_username(GUICtrlRead($Input_ip),GUICtrlRead($Input_usr))
      Else
         MsgBox($MB_SYSTEMMODAL, "function","an error occured")
      EndIf
   EndSwitch
WEnd

Func check_connection_usr($input_ip,$input_usr)
   GUICtrlSetData($connection_status_label, "checking")
   GUICtrlSetData($user_status_label, "checking")

   if $input_usr = "" then
      GUICtrlSetData($user_status_label, "no username!")
      Return False
   Else
      GUICtrlSetData($user_status_label, $input_usr & " will be checked")
      if $input_ip = "" then
         GUICtrlSetData($connection_status_label, "no IP or adres!")
         Return False
      else
         $check_ping = Ping($input_ip, 250)
         if not $check_ping Then
            GUICtrlSetData($connection_status_label, "error in ping")
            Return False
         Else
            GUICtrlSetData($connection_status_label, "ping = " & $check_ping & "ms.")
            return True
         EndIf
      EndIf
   EndIf
EndFunc

Func get_username($xIP,$usr)
   Local $ServerAddress = $xIP
   Local $ServerUserName = "root"
   Local $ServerPassword = ""
   Local $DatabaseName = ""

   $constrim="DRIVER={SQL Server};SERVER=" & $xIP & ";DATABASE=" & $DatabaseName & ";uid=" & $ServerUserName & ";pwd=" & $ServerPassword & ";"
   $adCN = ObjCreate ("ADODB.Connection") 
   $adCN.Open ($constrim) 
      MsgBox(0,"",$constrim )

      if @error Then
          MsgBox(0, "ERROR", "Failed to connect to the database")
          Exit
      Else
          MsgBox(0, "Success!", "Connection to database successful!")
      EndIf

      $sQuery = "select * from users where username=" & $usr

      $result = $adCN.Execute($sQuery)
      MsgBox(0, "", $result.username( " = username" ).Value) ;---------------------------is this ok ?
      GUICtrlSetData($user_status_label, $result.username & " username present");---------------------------is this ok ?
   $adCN.Close ; ==> Close the database
   
EndFunc

 

 

[mLipok]

If you want to use ADO start here:

 

EDIT: btw.

  On 3/24/2016 at 8:40 PM, FMS said:

but can't find anythin helpfull on the internet
Or iff I find something it is a little dated.

Expand  

I see the internet is far far away from this forum ?

 

Edited March 24, 2016 by mLipok

[mLipok]

  On 3/24/2016 at 8:40 PM, FMS said:

$result = $adCN.Execute($sQuery)  
MsgBox(0, "", $result.username( " = username" ).Value) ;---------------------------is this ok ?

Expand  

your $result is not special object, specialy prepared for your case.
This is Recordset. You can read about here: https://msdn.microsoft.com/en-us/library/ms681510(v=vs.85).aspx

You can use $oRecordset.GetRows() method to get your result as array.

 

[FMS]

I tried this ADO.au3 it did give some error's in the examples....
I did entered these errors on the downloadpage for you to see @mLipok
Thanks for the reply anyway

Also I need to say I'm willing to learn but don't know how to start.
This is also why i made this "example-script" so easy to learn so other people could have something of it.

I realy hope somebody could help me whit this and tell me how to get that name in the correct field

Edited March 27, 2016 by FMS

[FMS]

For futher references, I've got it working whit EzMySql

(iff somebody sees a flaw in this or does know a better/simpler way, I'm open for sugestions)
(I still have that injection-issue to work on :))

Here is the script working for me :

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
   #include "EzMySql.au3"
   #include <Array.au3>

$Form1 = GUICreate("Form1", 480, 100, 190, 320)
$button1 = GUICtrlCreateButton("check and run", 128, 24, 113, 33)
$Input_ip = GUICtrlCreateInput("127.0.0.1", 8, 16, 113, 21)
$Input_usr = GUICtrlCreateInput("harry", 8, 40, 113, 21)
$connection_label = GUICtrlCreateLabel("connection = ", 248, 16, 52, 17)
$user_label = GUICtrlCreateLabel("username = ", 248, 40, 52, 17)
$connection_status_label = GUICtrlCreateLabel("connection not checked", 304, 16, 190, 70)
$user_status_label = GUICtrlCreateLabel("user not checked", 304, 40, 190, 17)

GUISetState(@SW_SHOW)

While 1
   $nMsg = GUIGetMsg()
   Switch $nMsg
   Case $GUI_EVENT_CLOSE
      Exit
   Case $button1
      $checked = check_connection_usr(GUICtrlRead($Input_ip),GUICtrlRead($Input_usr))
      if $checked Then
         ;MsgBox($MB_SYSTEMMODAL, "function","true")
         get_username(GUICtrlRead($Input_ip),GUICtrlRead($Input_usr))
      Else
         MsgBox($MB_SYSTEMMODAL, "function","an error occured")
      EndIf
   EndSwitch
WEnd

Func check_connection_usr($input_ip,$input_usr)
   GUICtrlSetData($connection_status_label, "checking")
   GUICtrlSetData($user_status_label, "checking")

   if $input_usr = "" then
      GUICtrlSetData($user_status_label, "no username!")
      Return False
   Else
      GUICtrlSetData($user_status_label, $input_usr & " will be checked")
      if $input_ip = "" then
         GUICtrlSetData($connection_status_label, "no IP or adres!")
         Return False
      else
         $check_ping = Ping($input_ip, 250)
         if not $check_ping Then
            GUICtrlSetData($connection_status_label, "error in ping")
            Return False
         Else
            GUICtrlSetData($connection_status_label, "ping = " & $check_ping & "ms.")
            return True
         EndIf
      EndIf
   EndIf
EndFunc

Func get_username($xIP,$usr)


   If Not _EzMySql_Startup() Then
       MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
       Exit
   EndIf

   Local $hostname = "127.0.0.1"
   Local $SQLport = "3306"
   Local $dbname = "DBname"
   Local $usrname = "username"
   Local $Pass = "pass"

   If Not _EzMySql_Open($hostname, $usrname, $Pass, $dbname, $SQLport) Then
       MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
       Exit
   EndIf

   If Not _EzMySql_SelectDB($dbname) Then
       MsgBox(0, "Error setting Database to use", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
       Exit
   EndIf

   $aOk = _EzMySql_GetTable2d("SELECT username FROM users WHERE username = '"& $usr & "';")
   $error = @error
   If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error)

   if _EzMySql_Rows() = 0 then
       MsgBox(0, "check", "nothing found ; rows = " & _EzMySql_Rows()& " colums = " & _EzMySql_Columns())
       GUICtrlSetData($user_status_label, "no " & $usr & " found")
   Else
      MsgBox(0, "check", "array [1][0] = " & $aOk[1][0] & " rows = " & _EzMySql_Rows()& " colums = " & _EzMySql_Columns())
      GUICtrlSetData($user_status_label, $aOk[1][0] & " found!")
   EndIf

   _EzMySql_Close()
   _EzMySql_ShutDown()
EndFunc

 

[mLipok]

@FMS

as to your question:

  On 3/27/2016 at 10:40 PM, FMS said:

###############################
ADO.au3 v.2.1.13 BETA (970) : ==> COM Error intercepted !
$oADO_Error.description is:     [Microsoft][ODBC-stuurprogrammabeheer] De naam van de gegevensbron is niet gevonden en er is geen standaardstuurprogramma opgegeven
$oADO_Error.windescription:     Er is een uitzondering opgetreden.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  970
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################
###############################

 

tried your MySQL example in de zip.
But did give me this error.

Did i something wrong?
I only chaged the DB name ,username and pw, and ofcourse the query to something simple.

Expand  

 

In opening post your example uses SQLite udf

and MS SQL

   $constrim="DRIVER={SQL Server};SERVER=" & $xIP & ";DATABASE=" & $DatabaseName & ";uid=" & $ServerUserName & ";pwd=" & $ServerPassword & ";"
   $adCN = ObjCreate ("ADODB.Connection")

 

 

Then you comment about using MySQL example , now you are using EzMySQL

If you finally want to use  MySQL with ADO first you must to install ODBC Driver for MySQL
https://dev.mysql.com/downloads/connector/odbc/

The error description: 

[Microsoft][ODBC-stuurprogrammabeheer] De naam van de gegevensbron is niet gevonden en er is geen standaardstuurprogramma opgegeven
[Microsoft] [ODBC Driver Manager] The data source name is not found and no default driver specified

Cleary states that you have not installed this driver yet.

 

Edited March 28, 2016 by mLipok