Blank517

SQLite login

5 posts in this topic

Hi, I would like to make a login script with SQLite. The database looks like this:

_SQLite_Exec(-1, "CREATE TABLE Users (id INT(8) NOT NULL, username VARCHAR(30) NOT NULL, password VARCHAR(255) NOT NULL, permission INT(8) NOT NULL, PRIMARY KEY (id)); CREATE UNIQUE INDEX 'user_name_unique' ON 'Users' ('username' );")
_SQLite_Exec(-1, "INSERT INTO Users(id, username, password, permission) VALUES ('0', 'default', 'password', '0');")


theoretically I should make a query in this way, right?

_SQLite_Query (-1, "SELECT id FROM Users WHERE username = '" & $ Recv [1] & "' AND password = '" & $ Recv [2] & "';", $ hQuery)


but then I do not know how to know if you have found the 'id'

Share this post


Link to post
Share on other sites



Are you checking against $SQLITE_OK? Look at the example for _SQLite_Query in the help file.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
Just now, JLogan3o13 said:

Are you checking against $SQLITE_OK? Look at the example for _SQLite_Query in the help file.

I already tried but i think that $SQLITE_OK checks only if command was successful

Share this post


Link to post
Share on other sites

I guess I'm not understanding what you are asking then, it might be helpful to post all of your code. If I create a table based on the inputs you're looking for, like so:

#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $hDB, $myDBase = @TempDir & "\Test.tmp", $dllSQLite = @TempDir & "\sqlite.dll"

Local $sDLL = _SQLite_Startup($dllSQLite, False, 1)
    If @error Then
        ConsoleWrite("Failed with error: " & @error & @CRLF)
        Exit(-1)
    EndIf

$hDB = _SQLite_Open($myDBase)
    If $hDB = -1 Then Exit(MsgBox(0, "", "Error"))

    If $SQLITE_OK <> _SQLite_Exec($hDB, "CREATE TABLE Test ('ID', 'Username', 'Password', 'Permission');") Then
        MsgBox(0, "", "Error from TableCreate: " & _SQLite_ErrCode() & ". " & _SQLite_ErrMsg())
        Exit (-1)
    Else
        _SQLite_Exec($hDB, "INSERT INTO Test VALUES ('20418','Billy', 'Password1!', 'Administrator');")
        _SQLite_Exec($hDB, "INSERT INTO Test VALUES ('20419','Joe', 'Password2!', 'Operator');")
        _SQLite_Exec($hDB, "INSERT INTO Test VALUES ('20420','Jim', 'Password3!', 'User');")
        _SQLite_Exec($hDB, "INSERT INTO Test VALUES ('20421','Bob', 'Password4!', 'CEO');")
        _SQLite_Exec($hDB, "INSERT INTO Test VALUES ('20422','Sally', 'Password5!', 'Manager');")
        _SQLite_Exec($hDB, "INSERT INTO Test VALUES ('20423','Susie', 'Password6!', 'Mailroom');")
        _SQLite_Exec($hDB, "INSERT INTO Test VALUES ('20424','Sandra', 'Password7!', 'Secretary');")
    EndIf

_SQLite_Close()
_SQLite_Shutdown()

and then run a query against the values I've inserted, like this:

#include <MsgBoxConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $Recv[2] = ["Jimmy", "Password1!"], $hQuery, $aRow, $sMsg
Local $hDB, $myDBase = @TempDir & "\test.tmp", $dllSQLite = @TempDir & "\sqlite.dll"

Local $sDLL = _SQLite_Startup($dllSQLite, False, 1)
    If @error Then
        ConsoleWrite("Failed with error: " & @error & @CRLF)
        Exit(-1)
    EndIf

$hDB = _SQLite_Open($myDBase)
    If $hDB = -1 Then Exit(MsgBox(0, "", "Error"))

_SQLite_Query ($hDB, "SELECT id FROM Test WHERE username = '" & $Recv [0] & "' AND password = '" & $Recv [1] & "';", $hQuery)
    While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
        $sMsg &= $aRow[0]
    WEnd

    MsgBox($MB_SYSTEMMODAL, "SQL Query", (($sMsg = "") ? "Failed to return ID" : $sMsg))

_SQLite_Close()
_SQLite_Shutdown()

It will tell me if the ID is not found.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

@JLogan3o13 I love youuuu <3

You solved my problem, I didn't understand how to correctly use _SQLite_Query with _SQLite_FetchData...

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

    • Simpel
      By Simpel
      Hi,
      after I recognized that some of my scripts fail if they are at windows startup I researched for a solution. Problem could be fixed if I wait about 120 seconds after login. So how to measure this?
      #include-once #include <AD.au3> #include <Date.au3> ; #FUNCTION# ==================================================================================================================== ; Name ..........: _SecondsAfterLogin ; Description ...: Returns how many seconds are gone since last login ; Syntax ........: _SecondsAfterLogin() ; Parameters ....: None ; Return values .: Success - Integer of seconds ; Failure - 0, sets @error to 1 ; Author ........: Conrad Zelck ; Modified ......: ; Remarks .......: ; Related .......: ; Link ..........: ; Example .......: Yes ; =============================================================================================================================== Func _SecondsAfterLogin() Local $sDate Local $iSec $sDate = __GetLogonTime() ; UTC If @error Then $sDate = __GetLogonTime_AD() ; local time If @error Then Return SetError(1, 0, 0) Else $sDate = __LocalTime($sDate) ; UTC to local time EndIf $iSec = __TimeDifference($sDate) Return $iSec EndFunc #region - INTERNAL_USE_ONLY Func __GetLogonTime($sUserName = @UserName, $sComputerName = @ComputerName) ; Idea by trancexx: http://www.autoitscript.com/forum/topic/113611-if-isadmin-not-detected-as-admin/ Local $aRet = DllCall("netapi32.dll", "long", "NetUserGetInfo", "wstr", $sComputerName, "wstr", $sUserName, "dword", 11, "ptr*", 0) If @error Or $aRet[0] Then Return SetError(1, 0, 0) Local $sSeconds = DllStructGetData(DllStructCreate("ptr;ptr;ptr;ptr;dword;dword;dword;ptr;ptr;dword;dword;dword;dword;ptr;dword;ptr;dword;dword;byte;dword", $aRet[4]), 10) DllCall("netapi32.dll", "long", "NetApiBufferFree", "ptr", $aRet[4]) Local $sLastLogon = _DateAdd('s', Number($sSeconds), "1970/01/01 00:00:00") Return $sLastLogon EndFunc Func __GetLogonTime_AD() Local $iSuccess = _AD_Open() If $iSuccess = 1 Then Local $sDate = _AD_GetLastLoginDate() $sDate = __NumberDate_StringDate($sDate) _AD_Close() Return $sDate Else Return SetError(1, 0, 0) EndIf EndFunc Func __NumberDate_StringDate($sDate) Local $y, $m, $d, $h, $min, $s $y = StringMid($sDate,1, 4) $m = StringMid($sDate,5, 2) $d = StringMid($sDate,7, 2) $h = StringMid($sDate,9, 2) $min = StringMid($sDate,11, 2) $s = StringMid($sDate,13, 2) $sDate = $y & "/" & $m & "/" & $d & " " & $h & ":" & $min & ":" & $s Return $sDate EndFunc Func __LocalTime($sDate) Local $iTimeZoneOffset Local $aTimeZone = _Date_Time_GetTimeZoneInformation() If $aTimeZone[0] <> 2 Then $iTimeZoneOffset = ($aTimeZone[1]) * -1 Else $iTimeZoneOffset = ($aTimeZone[1] + $aTimeZone[7]) * -1 EndIf $iTimeZoneOffset /= 60 ; in hours Local $sHour = StringMid($sDate, 12, 2) $sHour += $iTimeZoneOffset $sDate = StringLeft($sDate, 11) & $sHour & StringRight($sDate, 6) Return $sDate EndFunc Func __TimeDifference($sDate) Local $sNow = _NowCalc() Local $sDiff = _DateDiff("s", $sDate, $sNow) Local $iDiff = Number($sDiff) Return $iDiff EndFunc #endregion - INTERNAL_USE_ONLY I had to look what kind of login it is. Some of my computers are logging in with active directory and other not.
      Here is an example:
      #include <SecondsAfterLogin.au3> Local $iSec = _SecondsAfterLogin() MsgBox(0, '', "Time since last login: " & _HHMMSS($iSec, "s")) Exit Func _HHMMSS($iTime, $sType = "ms") If $sType = "s" Then Local $sHHMMSS = StringFormat("%.2d:%.2d:%.2d", (Floor($iTime / 3600)), (Floor(Mod($iTime,3600) / 60)), (Mod(Mod($iTime,3600),60))) Else Local $sHHMMSS = StringFormat("%.2d:%.2d:%.2d", (Floor($iTime / 3600000)), (Floor(Mod($iTime,3600000) / 60000)), (Mod(Mod($iTime,3600000),60000) / 1000)) EndIf Return $sHHMMSS EndFunc I hope someone find it useful.
      Regards, Conrad
      P.S. Review is welcome.
    • Burgs
      By Burgs
      Greetings,
        I have SQLite setup within my AutoIT program...I'm trying to accomplish what should be a relatively simple task.  I want to be able to return an array of 'table' names for an established database...I believe this might be possible using the '_SQLite_SQLiteExe' command...since it seems to be able to access SQLite schemas...?  The ".tables" command is one of them...if I am not mistaken that command returns a list of all table names in the active database.
        I am attempting the following:
      #include <SQLite.au3> #include <SQLite.dll.au3> Global $hDb, $sIn, $sOut ... $sIn = ".tables" & @CRLF _SQLite_SQLiteExe($hDb, $sIn, $sOut) if @error == 0 Then ;Show Table (using SQLite3.dll) Else if @error == 2 Then ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF) Else ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF) EndIf ;@error is "2"...OR NOT... EndIf ;@error is "0"...OR NOT... ...   The error being thrown is "ERROR: Sqlite3.exe file not found" ...
        Am I required to have the Sqlite3.exe installed in my directory (i.e. @ScriptsDir)...???  I do not have it in there at present because I did not believe it was necessary with the 'include' calls to "SQLite.au3" and "SQLite.dll.au3"...any advice appreciated.  Thanks in advance. 
      Regards
       
       
       
       
       
       
       
    • kawliga751
      By kawliga751
      I am trying to script the opening of multiple tabs in IE with each tab having a separate login/password. I have been able to make a successful script that will open 3 separate IE sessions with the correct webpage and login or a script that will open 3 tabs in one session (which I want) but will not login. I have tried _IECreate, IEAttach etc. This is the script I am currently using . 
      #include <IE.au3> Const $navOpenInNewTab = 0x0800 Dim $oIE = _IECreate('http://asag.xxxxxxx.com/AAAA/index.htm') ;$o_IE.Navigate2('http://asag.xxxxxxx.com/apps/yyyyyyy/LogInSAG/login.asp', $navOpenInNewTab) ;$o_IE.Navigate2('http://asag.xxxxxxx.com/apps/yyyyyyy/LogInSAG/login.asp', $navOpenInNewTab) Call ("YYYYYYYSignIn") Func YYYYYYYSignIn () Local $username = _IEGetObjByName ($oIE,"uname") Local $password = _IEGetObjByName ($oIE,"pword") $oIE.Navigate2('http://asag.xxxxxxx.com/apps/xxxxxxx/LogInSAG/login.asp',2048) _IEFormElementSetValue ($username, "xxxxxxx") _IEFormElementSetValue ($password, "xxxxxxx") Send('{Enter}') EndFunc Call ("xxxxxxxSignIn") Func xxxxxxxSignIn () __IENavigate($oIE, "https://soa1gui.xxxxxxx.biz/gateway/NFServlet;jsessionid=1E3B8F6520DE0A1EC914A8ABB7E86341?NFH_MessageId=&NFH_Page=%2fpages%2fsearch%2fsearch.jsp", 1, 0x800) Local $domain = _IEGetObjByName ($oIE,"NF_CustomerID") Local $User_Name = _IEGetObjByName ($oIE,"NF_UserName") Local $Password = _IEGetObjByName ($oIE,"NF_Password") _IEFormElementSetValue ($domain, xxxxxxx") _IEFormElementSetValue ($User_Name, "xxxxxxx") _IEFormElementSetValue ($Password, "xxxxxxx") ;Send('{Enter}') EndFunc  
    • Fhelipe
      By Fhelipe
      Nothing More
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good evening guys
      I am working on a little project, in which I have to retrieve 8000+ rows of data from a table, from a SQLite database, to populate a combobox 
      This is what I tried 'til now, but it still takes about 12 seconds to populate the combobox.
      _SQLite_Exec($objDatabase, "BEGIN TRANSACTION;") If _SQLite_GetTable($objDatabase, "SELECT DISTINCT Comune FROM LISTA_COMUNI;", $arrRisultatoQuery, $intRighe, $intColonne) = $SQLITE_OK Then For $i = 2 To UBound($arrRisultatoQuery) - 1 If $i < UBound($arrRisultatoQuery) - 1 Then GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i] & "|") Else GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i]) EndIf Next _SQLite_Exec($objDatabase, "COMMIT;") Are there any other solution to retrieve 8000+ records from a SQLite database?
      Thank you very much