Jump to content

SQLite login


Blank517
 Share

Recommended Posts

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'

Link to comment
Share on other sites

  • Moderators

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

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

  • Moderators

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.

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
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
 Share

×
×
  • Create New...