Problems with Invite MySQL script

almighty · Post by **almighty** » Tue May 27, 2003 8:56 am

I have the following code

proc inv_1 { nick uhost hand arg } {
        global chan_1
        set found 0
        set arg [split $arg " "]
        set user1 [lindex $arg 0]
        set user1 [string range $user1 0 end]
        set pass1 [lindex $arg 1]
        set pass1 [string range $pass1 0 end]
        set data [sql "SELECT * FROM tbl_member WHERE username = '$user1' password = '$pass'"]
        if ( sql numrows($data) == 0) {
        putserv "PRIVMSG $nick :Invalid password or username!"
        putserv "PRIVMSG #ae-hc :\0034Invalid Login:\003 $nick (Username: $user1)"
        putcmdlog "$nick has failed to log in using $user1 as a username"
        } else {
        putserv "PRIVMSG #ae-hc :\0039Successful Login:\003 $nick (Username: $user1)"
        putquick "invite $nick $chan_1"
        putcmdlog "$nick has successfully logged in using $user1 as a username"

        }
}

The file loads fine except when i go to try to login nothing is sent back, the bot doesnt say ne thing. Ive checked the logs and there is no error message. Can ne 1 point me out where im going wrong?
Thanks
Almighty

ppslim · Post by **ppslim** » Tue May 27, 2003 9:07 am

Can you confirm the SQL module is logged into the server?

It is best to open and close the connection as needed.

However, this is dependant if the server is on the same machine, or over a long connection pipe.

almighty · Post by **almighty** » Tue May 27, 2003 9:37 am

This is the whole script

Code: Select all

load /sql/fbsql.so
sql connect **** **** "****"
sql selectdb almighty

bind msg - login inv_1


# Script starts now

proc inv_1 { nick uhost hand arg } {
        global chan_1
        set found 0
        set arg [split $arg " "]
        set user1 [lindex $arg 0]
        set user1 [string range $user1 0 end]
        set pass1 [lindex $arg 1]
        set pass1 [string range $pass1 0 end]
        set data [sql "SELECT * FROM tbl_member WHERE username = '$user1' password = '$pass'"]
        if ( sql numrows($data) == 0) {
        putserv "PRIVMSG $nick :Invalid password or username!"
        putserv "PRIVMSG #ae-hc :\0034Invalid Login:\003 $nick (Username: $user1)"
        putcmdlog "$nick has failed to log in using $user1 as a username"
        } else {
        putserv "PRIVMSG #ae-hc :\0039Successful Login:\003 $nick (Username: $user1)"
        putquick "invite $nick $chan_1"
        putcmdlog "$nick has successfully logged in using $user1 as a username"

        }
}

almighty · Post by **almighty** » Tue May 27, 2003 9:37 am

the sql server is on localhost

ppslim · Post by **ppslim** » Tue May 27, 2003 10:42 am

OK, I noticed quite a few things after that

1: You don't need the "string range" commands. These simply return exactly what went in. I am guessing you are trying to account for the much covered list to string issues. "lindex" returns only one element, as such, this is a string (unless you have lists in lists, which is irelivent in this case).

2: Check your first IF statment. I am guessing is is meant to read

Code: Select all

if {[sql numrows] == 0} {
OR
if {![sql numrows]} {

almighty · Post by **almighty** » Tue May 27, 2003 11:29 am

so u suggest i change the IF statement which ive now done, and also remove the string range commands?

Also if i remove the lindex commands where do i catch the username and password of the member?

ppslim · Post by **ppslim** » Tue May 27, 2003 5:11 pm

You keep the "lindex" commands, but not the "string range".

The "string range" commands are returning exactly what you put into them.

almighty · Post by **almighty** » Tue May 27, 2003 5:15 pm

right this is my new code

Code: Select all

load /sql/fbsql.so
sql connect localhost ***** "*****"
sql selectdb almighty


bind msg - login inv_1


# Script starts now

proc inv_1 { nick uhost hand arg } {
        global chan_1
        set found 0
        set arg [split $arg " "]
        set user1 [lindex $arg 0]
        set pass1 [lindex $arg 1]
        [sql "SELECT * FROM tbl_member WHERE username = '$user1' password = '$pass'"]
        if {[sql numrows] == 0} {
        putserv "PRIVMSG $nick :Invalid password or username!"
        putserv "PRIVMSG #ae-hc :\0034Invalid Login:\003 $nick (Username: $user1)"
        putcmdlog "$nick has failed to log in using $user1 as a username"
        } else {
        putserv "PRIVMSG #ae-hc :\0039Successful Login:\003 $nick (Username: $user1)"
        putquick "invite $nick $chan_1"
        putcmdlog "$nick has successfully logged in using $user1 as a username"

        }
}

however when i try and do /msg <bot> login <nick> <pass> it still doesnt work
ne ideas?

Papillon · Post by **Papillon** » Wed May 28, 2003 5:57 am

just a couple of things,
1. You don't need the [] brackets around the sql query, nothing wrong, just thought I'd say it

2. Don't use the ' in the query, say if the pass is sun, then '$pass' would return 'sun' not sun ... hope you see the difference

In TCL the ' char has no special meaning, and does not work like in php where it has the same use as "
3. Again nothing wrong, but no need to split the arg using " ", it'll default to that, all you need is [split $arg]

ppslim · Post by **ppslim** » Wed May 28, 2003 8:46 am

Papillon wrote:just a couple of things,
2. Don't use the ' in the query, say if the pass is sun, then '$pass' would return 'sun' not sun ... hope you see the difference In TCL the ' char has no special meaning, and does not work like in php where it has the same use as "

He has used this correctly.

It is not Tcl that needs the ' quote. It is MySQL.

Code: Select all

command hello and welcome to my world

If the above where Tcl code, how would it tell that is all one string, or 6 seperate ones, hell, even 3 strings in random sized groups.

This is easy to fix in Tcl with ".

However, the same applies to MySQL. In Tcl & PHP, you are using the SQL programming language within another language.

Just like in PHP and Tcl, MySQL distiguishes between what is part of a large string using quotes. In MySQL, you can use " and ' just like PHP.

His current usage with this is correct, for most compatability.

Papillon · Post by **Papillon** » Wed May 28, 2003 9:32 am

hehe just remembered myself.. was gonna come back and make it right, but as usual you are here to show us the way

almighty · Post by **almighty** » Wed May 28, 2003 1:36 pm

Code: Select all

bind msg - login inv_1


# Script starts now
load /home/almighty/eggdrop/scripts/sql/fbsql.so
set conn [sql connect localhost ***** "*****"]
sql selectdb almighty $conn
proc inv_1 { nick uhost hand arg } {
        global chan_1
        set arg [split $arg]
        set user [lindex $arg 0]
        set pass [lindex $arg 1]
        set stuff "SELECT * FROM tbl_member WHERE username = '$user' AND password = '$pass'"
        sql query $conn $stuff
        if {[sql numrows] == 0} {
        sql endquery $stuff
        putserv "PRIVMSG $nick :Invalid password or username!"
        putserv "PRIVMSG #ae-hc :\0034Invalid Login:\003 $nick (Username: $user1)"
        putcmdlog "$nick has failed to log in using $user1 as a username"
        } else {
        sql endquery $stuff
        putserv "PRIVMSG #ae-hc :\0039Successful Login:\003 $nick (Username: $user1)"
        putquick "invite $nick $chan_1"
        putcmdlog "$nick has successfully logged in using $user1 as a username"

        }
}
        sql disconnect $conn

This is the new code however i now get the error message
[13:12] Tcl error [inv_1]: can't read "conn": no such variable

spock · Post by **spock** » Wed May 28, 2003 8:11 pm

conn is defined outside the of procedure

( hint: global conn / $::conn )

ppslim · Post by **ppslim** » Thu May 29, 2003 4:49 am

I don't think you need $conn anyhow.

I thought fbsql provided multiple commands sets, rather than handle based connections?

IE, for a connection to SQL server 1, you use "sql", for SQL server 3, use "sql3".

almighty · Post by **almighty** » Thu May 29, 2003 3:59 pm

ok well ive removed the conn variable out of it however it now says
[20:55] Tcl error [inv_1]: sql query statement; you are not connected to a mysql server yet (sql connect).

the sql connect code is:
sql connect localhost user "pass"
sql selectdb almighty