Sign in to follow this  
Followers 0
Krustbox

Online verification via Mysql or PHP?

21 posts in this topic

#1 ·  Posted (edited)

So I'm trying to build a function into my script which will verify someone's copy of the program online somehow. I first looked up a MySQL UDF however I see this requires a driver to be installed, and I assume that the user themself would also have to install this driver on their own computer? That's way too much of a hassle, so i'm wondering if there is a way to have an autoit script connect to a php script in any way and use the PHP to do the MySQL verification process, then read the result back into the autoit script? I don't need a complete answer just wondering if this is possible and if so does anyone know of any examples of this? I've been searching for a while now but haven't come across anything. It should also be noted that I'm looking for this to be a completely behind the scenes type of verification, within the script itself only and not having a browser open or anything like that. Thanks.

As a simple example all i need is the autoit script to connect to example.com/example.php, and this page displays a simple "yes" or "no" text on a blank page which the script can then read into a variable for use. Hope this clears things up.

Edited by Krustbox

Share this post


Link to post
Share on other sites



I post this to make myself able to track this thread, it's what i was thinking about for while without success.

I have a Forums, ppl register there, and they should enter their Username and Password on my Software to be able to use it, else they need to register on my Forums. The Forums has a MySql db, the CMS is VBulletin.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Hi,

The way you suggested is pretty easy to do but not very secure, atleast use a secure connection (SSL) when you use this approach.

Anyways this proof of concept should get you started...

Important: This method is not secure and SHOULD NOT BE USED AS IS.

(The data is transmitted over an unsecure connection and also feeding it an 's' will spoof a successfull login and so on.)

AutoIt script [app_login.au3]:

#cs
    Proof of Concept :  AutoIt application login via the web.
#ce
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>


AutoItSetOption("GUIOnEventMode", 1)


Global Const $WL_RESULT_ERROR = 'e' ; this will be the page output on error (unable to connect to db)
Global Const $WL_RESULT_FAILED = 'f' ; this will be the page output on failure (invalid username/password)
Global Const $WL_RESULT_SUCCESS = 's' ; ; this will be the page output on success (valid username/password)

Global $wsLoginURL = 'http://localhost/labs/autoit/app-login/app_login.php' ; the URL to the PHP login page

Global $hLoginGUI, $hInputUsername, $hInputPassword, $hButtonLogin, $hLabelCreateAccount


$hLoginGUI = GUICreate(" :: Login", 232, 110, -1, -1, -1, BitOR($WS_EX_TOPMOST, $WS_EX_TOOLWINDOW))
GUICtrlCreateLabel("Username:", 16, 12, 64, 17)
GUICtrlSetFont(-1, 9, 800, 0, "MS Sans Serif")
$hInputUsername = GUICtrlCreateInput("", 96, 8, 121, 21)
GUICtrlSetLimit(-1, 20)
GUICtrlSetTip(-1, "Please enter your username")
GUICtrlCreateLabel("Password:", 16, 44, 62, 17)
GUICtrlSetFont(-1, 9, 800, 0, "MS Sans Serif")
$hInputPassword = GUICtrlCreateInput("", 96, 40, 121, 21, BitOR($ES_PASSWORD, $ES_AUTOHSCROLL))
GUICtrlSetLimit(-1, 20)
GUICtrlSetTip(-1, "Please enter your password")
$hButtonLogin = GUICtrlCreateButton("Login", 144, 72, 75, 25)
GUICtrlSetCursor(-1, 0)
$hLabelCreateAccount = GUICtrlCreateLabel("Create account", 16, 80, 77, 17)
GUICtrlSetColor(-1, 0x0000FF)
GUICtrlSetCursor(-1, 0)
GUISetOnEvent($GUI_EVENT_CLOSE, "_Event_GUIClose")
GUICtrlSetOnEvent($hButtonLogin, "_Login")
GUICtrlSetOnEvent($hLabelCreateAccount, "_CreateAccount")
GUISetState(@SW_SHOW, $hLoginGUI)


While 1
    Sleep(50)
WEnd


Func _Login()
    Local $sUsername = GUICtrlRead($hInputUsername), $sPassword = GUICtrlRead($hInputPassword)
    If Not StringStripWS($sUsername, 8) Or Not StringStripWS($sPassword, 8) Then ; no username and/or password entered
        MsgBox(262192, "Warning", "You must enter a username and password")
        Return
    EndIf

    Switch _LoginCheck($sUsername, $sPassword, $wsLoginURL)
        Case $WL_RESULT_ERROR ; remote errror
            MsgBox(262160, "Error", "The server encountered an error, please try again later.")
        Case $WL_RESULT_FAILED ; login failed
            MsgBox(262192, "Failed", "Invalid username and/or password")
        Case $WL_RESULT_SUCCESS ; login was successfull
            MsgBox(262208, "Success", "Your credentials seem to be valid.")
            ; here you can set a var to true or something to check in the rest of the script
        Case Else ; unexpected return
            MsgBox(262160, "Unexpected Result", "Something went wrong")
    EndSwitch
EndFunc   ;==>_Login

Func _LoginCheck($sUsername, $sPassword, $sURL)
    Local $oHTTP = ObjCreate("winhttp.winhttprequest.5.1") ; create HTTP object
    $oHTTP.Open("POST", $sURL, False) ; set method (POST) and URL
    $oHTTP.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
    $oHTTP.SetRequestHeader("Connection", "keep-alive")
    $oHTTP.SetRequestHeader("User-Agent", "MyApp/1.0")
    $oHTTP.Send("username=" & $sUsername & "&password=" & $sPassword) ; set username and password then send the request
    If $oHTTP.Status == 200 Then Return SetError(0, 0, $oHTTP.ResponseText) ; return response text
    Return SetError(1, $oHTTP.Status, 0)
EndFunc   ;==>_LoginCheck

Func _CreateAccount()
    ShellExecute('http://example.com')
EndFunc   ;==>_CreateAccount

Func _Event_GUIClose()
    Exit 0
EndFunc   ;==>_Event_GUIClose

PHP script [app_login.php]:


<?php
/*
Proof of Concept : AutoIt application login via the web.
*/
error_reporting( E_ALL | E_STRICT );
ini_set( 'display_errors', '1' );

// this will be our simple output
define( 'OUTPUT_ERROR', 'e' );
define( 'OUTPUT_FAILED', 'f' );
define( 'OUTPUT_SUCCESS', 's' );
// salt for password hashing
define( 'PASSWORD_SALT', '~921264b0-3F2E-11E1-B86C-0800200c9a66~' );

// database details
$db_hostname = "localhost";
$db_username = "root";
$db_password = "pass";
$db_database = "autoit";
$db_table = "app_login";

// Change the patterns to match allowed usernames/passwords for your site,
// replace the numbers in the {2,20} part with your min/max length {min,max}
// Allows usernames of 2 to 20 of the following characters a-z A-Z 0-9 _ + = -
// and password of 2 to 20 printable characters
if( isset( $_POST['username'] ) && isset( $_POST['password'] ) ) {
if( !preg_match( "/^[w=+-]{2,20}$/i", $_POST['username'] ) || !preg_match( "/^[[:print:]]{2,20}$/i", $_POST['password'] ) ) {
die( OUTPUT_FAILED );
}

$db_link = mysql_connect( $db_hostname, $db_username, $db_password );
if( !is_resource( $db_link ) || !mysql_select_db( $db_database, $db_link ) ) {
die( OUTPUT_ERROR );
}

$username = mysql_real_escape_string( $_POST['username'] );
$password = sha1( md5( PASSWORD_SALT ) . $_POST['password'] . PASSWORD_SALT);
$query = "SELECT * FROM `{$db_table}` WHERE `username`='{$username}' AND `password`='{$password}'";

$result = mysql_query( $query, $db_link );
if( !$result || !mysql_num_rows( $result ) == 1 ) {
die( OUTPUT_FAILED );
} else {
echo OUTPUT_SUCCESS;
}
} else {
die( OUTPUT_FAILED );
}
?>
MySQL Table (username=test, password=test) :CREATE TABLE `app_login` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,`username` VARCHAR( 20 ) NOT NULL ,`password` VARCHAR( 128 ) NOT NULL) ENGINE = InnoDB;INSERT INTO `app_login` (`id`, `username`, `password`)VALUES ('1', 'test', '5bc60cffbeb09ac9f15fb81833d89f9028298dab');

Note: To make this suitable for vBulletin or the likes the password hashing logic in the PHP script must be rewritten to match that of the forum software.

Edited by Robjong
1 person likes this

Share this post


Link to post
Share on other sites

Thank you very much for taking the time to post these, I should be able to piece together a working script from all of this to suit my need. Thanks again I'll post an update or question as I go along :)

Share this post


Link to post
Share on other sites

No problem.

There are some comments in the scripts that will help you understand what is going on but it is all pretty straight forward.

If you have any questions feel free to ask.

Good luck.

Share this post


Link to post
Share on other sites

Krustbox: Keep in mind that the end-user's computer might not have the ability to access your web page. It might be because of Internet monitoring software, proxy software or complete blocking. I recommend that you inform your users that they require Internet access to your site for authorization. Depending on your clients you might want to develop another method of authorizing your software as a second method.

(I don't have an F7 key)

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

I will post my question after i completed autoit at my school.

Edited by kid1519

Share this post


Link to post
Share on other sites

A couple of 'step up security' things I did in a php-only project that I'm now porting to AutoIt (the php code required a bit too much 'techie' from the user end, and had other issues, so porting it all over......)

AutoIt side security code not yet started - too many other things to do getting the features working, then I'll tie in the security stuff just before public release, so below is just 'concept', though it works well in the php-only version.  Also note that this is a bit of 'overkill' for the OP's question, though relates to the subject and other question about a 'Microsoft-ish' code.  

This is only one way to do it - the more 'mixed' you get, the better!

'Simple' Online Security Check To Create Unlock Code For Only One Machine

IMPORTANT: The first thing for you to understand is that NO method of encryption, obfuscation or anything else will guarantee the code won't be cracked.  Much like putting a security system in a house, there are various 'layers' you can provide to deter a 'common thief', but a determined pro can still get in.  What you are looking to achieve is something that will make a hacker think "it is easier to just buy it than to crack it" - kind of like putting a lock on a glass window (generally, what is the point? though, it will give protection to some level)

NEVER put anyone's personal information at risk by including it in your security check!

(end of soapbox speech ... ;)

Note: this method does not 'require' an ssl connection, but it never hurts!

1. read the details from the machine they are on (there's a nice UDF for that) - UUID, board info, CPU, drive info, etc. (NO PERSONAL DATA!)

2. sprinkle in your feature unlock bits throughout the generated code (this serves to further 'lock the window'), putting the bits scattered around where only you know where they are (do not put them 'every 3rd character' or something - make it more difficult!)  One thing you can do here is to take the first (for instance - be creative and use what you like!) character and 'key' off that to further tell where the feature bits are hiding (so, if the first character is '4', you might divide the string by 4, then look at the second character and go that many characters ahead, etc. - yes, it gets confusing - isn't that the point???  But, remember that you only have to do this once - for each side, then the programs talk automatically.)

3. encrypt that data using your own known 'salt' (there's a UDF for that...) - what you will come up with is a string that is something like 200 characters long (recommended to be 'long', but not 'massive' - you want it to transfer quickly)

4. send that string to the php side (similar to the above code)

5. the php side unravels what you put together into a) 'ID' code and B) 'Features' code (just like you put in)

6. Check the 'ID' code against the SQL server (I suggest you break out all the UUID, disk info, board, etc. and then do a 'percentage match' in case the user changes out a drive or something later)

7. Create a 'valid' string for various features (for instance, if the user has purchased the feature from a website, turn that on and next time they run the security check, that feature will be added)

================== NOW, DO IT IN REVERSE! ============================

8. Use a DIFFERENT 'salt' and 'secret mix' in the server to create a string to return to the user computer.

9. Unravel that string in the local software and turn on the features.

See, 'simple'!  (less than 10 steps!)  ;)

1 person likes this

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

<snip>

teach me, create a .php and .sql for check Edited by Melba23
Removed quote content

Share this post


Link to post
Share on other sites

kid1519,

When you reply, please use the "Reply to this topic" button at the top of the thread or the "Reply to this topic" editor at the bottom rather than the "Quote" button - we know what we wrote and it just pads the thread unneccessarily. ;)

M23

1 person likes this

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

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

teach me, create a .php and .sql for check

I believe that shows the base very well, so I believe 'create a .php and .sql for check' has been addressed.

I also believe the 'teach me' has been addressed in my addition of ideas/suggestions/etc that can make the base code more secure.

Perhaps your 'teach me' means 'show me where I could do better in the code'.  If so, you will find help here - by showing your code and asking for help in areas you get stuck.

It seems, though, that your 'teach me' means more 'do it for me' - that is not going to happen, for multiple reasons including the fact that creating security for your product is best done yourself (through taking the ideas/hints/suggestions given in this thread).  Certainly any code I create for security on my products will not be posted publicly and anyone that would post such would be wasting their time in creating it!

So, this is an area where 'ask for help when you get stuck' is the best (and IMHO, only) approach to going further.

1 person likes this

Share this post


Link to post
Share on other sites

kid1519,

When you reply, please use the "Reply to this topic" button at the top of the thread or the "Reply to this topic" editor at the bottom rather than the "Quote" button - we know what we wrote and it just pads the thread unneccessarily. ;)

M23

I hope i do true. Like this... or different.

I will careful.

Thanks

Share this post


Link to post
Share on other sites

I believe that shows the base very well, so I believe 'create a .php and .sql for check' has been addressed.

I also believe the 'teach me' has been addressed in my addition of ideas/suggestions/etc that can make the base code more secure.

Perhaps your 'teach me' means 'show me where I could do better in the code'.  If so, you will find help here - by showing your code and asking for help in areas you get stuck.

It seems, though, that your 'teach me' means more 'do it for me' - that is not going to happen, for multiple reasons including the fact that creating security for your product is best done yourself (through taking the ideas/hints/suggestions given in this thread).  Certainly any code I create for security on my products will not be posted publicly and anyone that would post such would be wasting their time in creating it!

So, this is an area where 'ask for help when you get stuck' is the best (and IMHO, only) approach to going further.

Thank you so much

I am reading and test with your code. 

I will question at your thread later

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

I believe that shows the base very well, so I believe 'create a .php and .sql for check' has been addressed.

I also believe the 'teach me' has been addressed in my addition of ideas/suggestions/etc that can make the base code more secure.

Perhaps your 'teach me' means 'show me where I could do better in the code'.  If so, you will find help here - by showing your code and asking for help in areas you get stuck.

It seems, though, that your 'teach me' means more 'do it for me' - that is not going to happen, for multiple reasons including the fact that creating security for your product is best done yourself (through taking the ideas/hints/suggestions given in this thread).  Certainly any code I create for security on my products will not be posted publicly and anyone that would post such would be wasting their time in creating it!

So, this is an area where 'ask for help when you get stuck' is the best (and IMHO, only) approach to going further.

okay, but you know: it has very much "<br />"

i can not read and understand.

Mr Robjong post a script and code but it is different for read

 

Edited by kid1519

Share this post


Link to post
Share on other sites

Dear Mr Melba23
Now i understand.
i need do like this.
okay,

sorry because last time i didn't know this.
have a nice day

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

I will post my question after i completed autoit at my school.

Edited by kid1519

Share this post


Link to post
Share on other sites

nicely documented on what you have done/tried (shows you aren't just looking for a total hand-out solution, I like that)

however, it seems you missed on one of the crucial points - WHAT HAPPENED that you don't like?

You show the user/pass in the database, so congrats on getting that done, but " i also can not use it." tells little about what you were expecting to happen, etc.  Give a bit more info on what you think should happen next and what DID happen.  As I see the code, when you try to login, one of four responses should come back;

"Error", "The server encountered an error, please try again later."

"Failed", "Invalid username and/or password")

"Success", "Your credentials seem to be valid.")

"Unexpected Result", "Something went wrong")

 

 

So, just what response are you getting when you go to log in?
 

Also, I don't know (or personally care) if you are showing your real login details in the code, but you should be very careful to NEVER disclose such information.  The standard way to 'hide' them is to change any/all references to a website to 'www.example.com', passwords to 'mypass' (you have that, but the rest looks like it might be real - if so, that is a horrible password to use on a live system).  Perhaps the posted info is not real and you are safe with it, just pointing it out.....

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

 

nicely documented on what you have done/tried (shows you aren't just looking for a total hand-out solution, I like that)

however, it seems you missed on one of the crucial points - WHAT HAPPENED that you don't like?

You show the user/pass in the database, so congrats on getting that done, but " i also can not use it." tells little about what you were expecting to happen, etc.  Give a bit more info on what you think should happen next and what DID happen.  As I see the code, when you try to login, one of four responses should come back;

"Error", "The server encountered an error, please try again later."

"Failed", "Invalid username and/or password")

"Success", "Your credentials seem to be valid.")

"Unexpected Result", "Something went wrong")

 

 

So, just what response are you getting when you go to log in?
 

Also, I don't know (or personally care) if you are showing your real login details in the code, but you should be very careful to NEVER disclose such information.  The standard way to 'hide' them is to change any/all references to a website to 'www.example.com', passwords to 'mypass' (you have that, but the rest looks like it might be real - if so, that is a horrible password to use on a live system).  Perhaps the posted info is not real and you are safe with it, just pointing it out.....

My teacher, 
Thank you so much
All pictures and codes above, this is a example of me.
Don't worry. My teacher.
My host and database is free. They are used for test.
If you need, i can share for you password and you can test and repair for me.
No problem.
With case: I always receive: "Failed", "Invalid username and/or password")
Very poor for me.
Althought, i create a .sql and .php at a other project, and they can use. 
But at this thread. i can't use
Repair and bug for me. 
I need use .php and .sql like above for new project:
Can you see those pictures:
Oh, maybe i will sent for you a message.
Please take the time and read it.
thank you so much

 

Edited by kid1519

Share this post


Link to post
Share on other sites

you stated

 

Username: test
Pass: 5bc60cffbeb09ac9f15fb81833d89f9028298dab

 

though the instructions on the code you copied clearly stated

 

MySQL Table (username=test, password=test) :

you need to use the password as instructed when you log in.

Why?

$username = mysql_real_escape_string( $_POST['username'] );
   $password = sha1( md5( PASSWORD_SALT ) . $_POST['password'] . PASSWORD_SALT);
   $query = "SELECT * FROM `{$db_table}` WHERE `username`='{$username}' AND `password`='{$password}'";

$username is not changed in any way (taken directly from the POST data and checked against the database)

$password IS changed from the POST data, encrypted prior to being saved in the database (which stops people from getting into your database and stealing all the passwords - including YOU....)

In other words, the 'fix' is for you to follow the instructions given and use 'test' as a password and NOT what you see in the database (and your 'abc' user will never be able to log in as the password of '123' is not properly encrypted, so you might as well delete it).  If you want to add an account, add it using the AutoIt function and NOT directly in the database.

1 person likes this

Share this post


Link to post
Share on other sites

you stated

though the instructions on the code you copied clearly stated

you need to use the password as instructed when you log in.

Why?

$username = mysql_real_escape_string( $_POST['username'] );
   $password = sha1( md5( PASSWORD_SALT ) . $_POST['password'] . PASSWORD_SALT);
   $query = "SELECT * FROM `{$db_table}` WHERE `username`='{$username}' AND `password`='{$password}'";

$username is not changed in any way (taken directly from the POST data and checked against the database)

$password IS changed from the POST data, encrypted prior to being saved in the database (which stops people from getting into your database and stealing all the passwords - including YOU....)

In other words, the 'fix' is for you to follow the instructions given and use 'test' as a password and NOT what you see in the database (and your 'abc' user will never be able to log in as the password of '123' is not properly encrypted, so you might as well delete it).  If you want to add an account, add it using the AutoIt function and NOT directly in the database.

Oh, username=test, password=test: i see.

Thank you so much my teacher

I try and repair them.

thanks

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
Sign in to follow this  
Followers 0

  • Similar Content

    • Fhelipe
      By Fhelipe
      Nothing More
    • Jefrey
      By Jefrey
      I've made this HTTP lib to simplify HTTP requests, mainly when dealing about POST data or file uploads.
      Three functions are available:
      string _HTTP_Get ( string $sURL ) string _HTTP_Post ( string $sURL , string $sPostData ) string _HTTP_Upload ( string $sURL , string $sFilePath , string $sFileField , string $sPostData = '' , string $sFilename = Default) Additionaly, two helper functions are also available:
      URLEncode($sStr) URLDecode($sStr) Full documentation: https://github.com/jesobreira/HTTP.au3/blob/master/README.md
      Fork me on Github: https://github.com/jesobreira/HTTP.au3
      Download lib + docs: https://github.com/jesobreira/HTTP.au3/archive/master.zip
    • rynow
      By rynow
      romaSQL
      This autoIt UDF is built on the concept of Laravel Query & doctrine.
      RomaSQL provides a new, comfortable and easy to use way for SQL-queries in autoIt.
      Most of the common SQL-queries are supported already and more are coming soon.
      All of your support is much appreciated.
      Connections
      For the connection the object ADODB is used. Therefore the connection string is based on ODBC.
      You can also use OLEDB connection strings or other database connections.
      In order for this to work your add-ons have to be installed in the function: __4ern_SQL_Connection.
      I’d be very glad if you shared your modifications with me.
      Currently supported connections
      -       MySQL (odbc)
      -       Microsoft SQL Server (odbc)
      -       SQLite (odbc)
      -       Microsoft Access (odbc)
      Command reference
      $SQL_connect; establishing connection $SQL_returnType; return a Array or Dictionary ('oDict') Object (Default = Array) $SQL_setDefaultTable; Default Tablename $SQL_setDefaultKey; Default Colmn Key (Default = id) $SQL_debug; if True, show SQL Statment in Console $SQL_get $SQL_update $SQL_delete $SQL_insertInto $SQL_take $SQL_limit $SQL_table $SQL_select $SQL_distinct $SQL_where $SQL_orWhere $SQL_whereBetween $SQL_whereNotBetween $SQL_whereIn $SQL_whereNotIn $SQL_whereNull $SQL_whereNotNull $SQL_having $SQL_orHaving $SQL_havingBetween $SQL_havingNotBetween $SQL_havingIn $SQL_havingNotIn $SQL_havingNull $SQL_havingNotNull $SQL_groupBy $SQL_orderBy  
      Examples
      establishing connection
      ;-----/ ; SQLite Connection ;-----/ $SQL_setDatabase('sqlite') $SQL_connect('C:\project.db') ;-----/ ; Access Connection ; Database, User, Password ;-----/ $SQL_setDatabase('access') $SQL_connect('C:\project.mdb') ;or as Admin $SQL_connect('C:\project.mdb', '4ern', 'root') ;-----/ ; SQLServer Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('sqlserver') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'SQL Server') ;-----/ ; MySQL Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('mysql') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'MySQL ODBC 5.2 UNICODE Driver')  
      simple SQL query
      $SQL_table('albums') $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      Select
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song') ;or pass to an Array Local $aSelect = ['id', 'Name', 'Artist', 'Song'] $SQL_select($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif where
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song', 'Votes') $SQL_where('Artist', 'adele') $SQL_where('Votes', '>=' ,'9') $SQL_orWhere('Artist', '=' ,'Rag'n'Bone Man') ;or pass to an 2dArray Local $aSelect = [['Artist','adele'],['Votes', '>=' ,'9']] $SQL_where($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      If you need more examples, then tell me exactly what you need.
      I hope you like my UDF and find some use for it.
      ---
      ->DONWLOAD romaSQL
       
       
    • macran
      By macran
      I use mysql.au3 to connect mysql database .  If restart the server ,the client how to reconnect mysql?
      The function prototype is
      _MySQL_Options($MySQL_ptr, $option, $arg = 0, $type = "ptr")
      so,
      _MySQL_Options($MysqlConn,$MYSQL_OPT_RECONNECT, ?, ?)
      pls help me,thanks!
       
       
       
    • sivaramanm
      By sivaramanm
      From AutoIT script (Pretty much same syntax as VBA), Tried connecting to MySQL Server. While i am able to insert a new row successfully, unable to verify the rowcount (# of inserted row - to verify success or failure).
      Have tried two different methods -
      to use the RecordsAffected variable from Connection Execute function to use the RecordSet and retrieve the rowcount But have been missing something and none of these methods return the actual row count.
      Any help would be appreciated.!!!
      Cross-posted in http://stackoverflow.com/questions/27411599/unable-to-retrieve-inserted-row-count-in-mysql-using-ado-from-autoit
      MySQLConnect() $EVENT_TIME= "2014-12-12 12:12:12" $LSMCName='LSMC1' $NEType='MME0001' $OMTarFile='A_MME0011-60MIN-20141212-12-v.tar' $CSVFile='12-00-S1AP.csv' $KPIType='S1AP_HO' $UpdateStatus='NotUpdated' $ReTries='0' If Not (InsertFileUpdateLog($EVENT_TIME,$LSMCName,$NEType,$OMTarFile,$CSVFile,$KPIType,$UpdateStatus,$ReTries)=1) Then WriteLog("[Error] Record insertion failed for " & $EVENT_TIME & '" ' & $LSMCName & ' ' & $NEType & ' ' & $OMTarFile & ' ' & $CSVFile & ' ' & $KPIType & ' ' & ' NotUpdated 0') EndIf MysqlDisconnect() ;~ ####################### Sub Function Definitions Func MySQLConnect() Local $sDriver="MySQL ODBC 5.3 ANSI Driver" Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $sDriver) If @error or $val = "" Then SetError(2) Return -1 EndIf $constrim="DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=localhost;DATABASE=pmdemo;uid=rootuser;pwd=rootpass;" $oDBConnect = ObjCreate ("ADODB.Connection") ; <== Create SQL connection $oDBConnect.Open ($constrim) ; <== Connect with required credentials if @error Then WriteLog("[Error] Failed to connect to the database") SetError(2) Return -2 Else ;MsgBox(0, "Success!", "Connection to database successful!") Return 1 EndIf EndFunc Func MySQLDisConnect() $oDBConnect.Close ; ==> Close the database EndFunc Func InsertFileUpdateLog($EVENT_TIME,$LSMCName,$NEType,$OMTarFile,$CSVFile,$KPIType,$UpdateStatus,$ReTries) Local $RowCount = 0 Local $result = ObjCreate("ADODB.Recordset") $sQuery = "INSERT INTO 4gc_fileupdatelog (id,EVENT_TIME,LSMC,NEType,TarFile,CSVFile,KPIFile,UpdateStatus,ReTries) VALUES ('0'," & _ "'" & $EVENT_TIME & "'," & _ "'" & $LSMCName & "'," & _ "'" & $NEType & "'," & _ "'" & $OMTarFile & "'," & _ "'" & $CSVFile & "'," & _ "'" & $KPIType & "'," & _ "'" & $UpdateStatus & "'," & _ "'" & $ReTries & "'" & _ ") ON DUPLICATE KEY UPDATE ReTries=ReTries+1,UpdateStatus='" & $UpdateStatus & "';" $result = $oDBConnect.Execute($sQuery,$RowCount) If @error Then MsgBox(1,1,"Error executing query...") Return -2 EndIf ;# Method-1 : To use records affected from Execute function If $RowCount >= 1 Then MsgBox(1,1,"Success") Else MsgBox(1,1,"Failed, rowcount is:" & $RowCount ) EndIf If Not ($result.bof AND $result.eof) Then WriteLog("[Error] No Rows found") Return 0 EndIf ;# Method-2 : To use recordsset object and retrieve the rows/columns count If IsObj($result) And $result.EOF=False Then $myarray=$result.GetRows() $rows = UBound($myarray,1) $cols = UBound($myarray,2) MsgBox(1,1," rows: " & $rows & " cols: " & $cols) If ($rows = 1) Then WriteLog("[Info] Record inserted successfully") Return 1 ElseIf ($rows = 2) Then WriteLog("[Alert] Record updated successfully. affected row(s) is " & $rows) Return $rows Else > Blockquote WriteLog("[Error] Record insertion failed. affected row(s) is " & $rows) Return 0 EndIf EndIf EndFunc