FMS

autoit and using / connecting SQL DB

6 posts in this topic

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
#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

 

 


as finishing touch god created the dutch

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

If you want to use ADO start here:

 

EDIT: btw.

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

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

;)

 

Edited by mLipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites
1 hour ago, FMS said:

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

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.

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by FMS

as finishing touch god created the dutch

Share this post


Link to post
Share on other sites

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 :

#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

 


as finishing touch god created the dutch

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

@FMS

as to your question:

21 hours ago, 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.

 

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 by mLipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Similar Content

    • Skeletor
      By Skeletor
      MS Splash Screens
      The office 2016/17 office suite has made many people turn heads with its functions and especially their splash screen.
      I now present to you, the MS Style Splash Screen. For Rookies, Novices and anyone who wants a ready made splash screen for your gui application.

      You can now download it and also try it out yourself.
      You have three colors to choose from:
      Blue
      Red
      Green
      All three colours are from the famous programs.
      I also included the KODA form to you can have freedom with this splash screen.
      Note: the KODA form does not have the three colours as well as the correct blue. 

      Have fun and enjoy.
      Note:
      I will update the splash to add the automated 3 dots animation at a later stage. 
       
       
    • Skeletor
      By Skeletor
      A gui splash screen. Themed after the famous MS Office Suite.
      Three colours to choose from, Red, Blue and Green.
      You can change the labels to your own application.

      Also bundled with this is the KODA form. So you can open up and see the basic structure of this splash screen.
       
      Splash Screen GUI.zip
    • BlazerV60
      By BlazerV60
      Hello all,
      I've been trying to figure out how to launch Google Chrome in the background (hidden) but it doesn't seem possible. I've tried the following methods:
      ShellExecute("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe", "", "", "", @SW_HIDE) Also I've tried:
      ShellExecute(@ComSpec, "/c start chrome.exe","","",@SW_HIDE) Lastly I tried:
      RunWait('"'&@ProgramFilesDir&'\Google\Chrome\Application\chrome.exe" --silent-launch',@ScriptDir)  
      But all of them launch my chrome browser without hiding it. Does anyone know a workaround for this or if AutoIT just can't Chrome? 
      Bonus points if you know how to make it launch chrome hidden and make it go to https://www.autoitscript.com
       
      Thank you,
      Brian
    • SteveJM
      By SteveJM
      I have a working program currently driven largely through menu selections. I would like to add a toolbar where most of the tollbar button actions are basically the same as menu items, but quicker to access. I have been wading around in toolbar examples, MSDN pages etc, it's clearly going to be a bit of a slog to get everything right, including tooltips etc. I thought I would start with something simple to prove the principle.
      Using bits from the help file examples I have a small program that successfully displays a toolbar. However, what seemed like the most elegant way to deal with the button commands does not seem to work. My understanding was that a toolbar button fires a WM_COMMAND message, with the command Id set by the second parameter in the call to
      _GUICtrlToolbar_AddButton ( $hWnd, $iID, $iImage)
      so I though it would be a good idea to set this Id to the same value as my menu item Id; then it would run the same task which is what I wanted. This did not work. I am using message loop mode and would like to stick with this because some of my scripts run hardware at the same time as the gui; it is easier if I don't have to worry about code being interrupted with the hardware in an unknown state . So I added a handler for WM_COMMAND, with some cribbed display code to try and see why. The toolbar button defintely fired a WM_COMMAND message and the Id looked the same, so no explanation there.
      I guess the issue is with GUIGetMsg() which may be constructed to ignore all but a limited number of control handles, i.e. those made with the GuiCtrlCreate... commands; this is speculation.
      I would dearly love to find a tidy way to get around this. Having some controls handled in the message loop and some in a WM_COMMAND handler, performing the same task, feels ugly. I would be very grateful for further insight from someone experienced with handling a toolbar. Perhaps I should be trying to fire the menu item. I have attached a code snippet to try and illustrate the issue.
       
       
       
      ToolbarTrial.au3
    • XinYoung
      By XinYoung
      Hello all,
      I've been working on 4 standalone scripts. They each have their own GUI and are now independent .exe's. But instead of having 4 separate icons on my computer, is it possible to put them all together into one .exe?
      What I want is one interface with tabs. Each script's GUI would be on their own respective tab. Is this possible?
      The different scripts don't need to communicate with one another. I just want them all in one .exe.
      I can't find this anywhere... Any help would be greatly appreciated. Thank you!