Recursive File search (possibly via database?) [solved!]

[DJCharlie]

Here's exactly what I have:

Code: Select all

foreach query [mysqlsel $sqlhand "SELECT * FROM `music` WHERE `tracknum` = [::mysql::escape $text]"] {

And what I get is:

Code: Select all

-FredGibson- Sending you Song #1:  -

That query is valid in MySQL, but not here...

[DJCharlie]

Ok, trying a different tack...

Code: Select all

proc pub_fetch {nick host hand chan text} {
   global db
   set sqlhand [mysqlconnect -host $db(hostname) -user $db(username) -password $db(password)]
   mysqluse $sqlhand $db(database)
   set query [mysqlsel $sqlhand "SELECT `path` FROM `music` WHERE `tracknum` = '$text'"]
   mysqlclose $sqlhand
   putlog "[lindex $query 1]"
}

The SQL is sound, but still gets translated to nothing. Any ideas?

[caesar]

What's the setup of the database and what's the output of $query?

[DJCharlie]

4 fields: tracknum (int11), artist (text), title (text), and path (text).

The output of $query is 1. Every time, no matter what I input, it's 1.

I've gotten a temporary fix by going back to exec'ing a bash script that does the lookup instead of the tcl, but I'd like to do away with that.

[doggo]

Code: Select all

# Rls: #mysql_search.tcl
# Date: 24/07/10
# Coded by: doggo
# Contact: #alt.binaries.inner-sanctum@EFNET
############################################

package require mysqltcl 3.05

#channel flag    .chanset #YOUR_CHAN +mysql-search

setudef flag mysql-search

#connect to db

set db(host) "******"
set db(user) "******"	
set db(pass) "******"
set db(name) "******"

#table info

set t(tracknum) "tracknum"
set t(artist) "artist"
set t(title) "title"
set t(path) "path"

#from table

set t(table) "egg"

#output channel

set output_channel "#allscene"



###script starts###

# the help section 

bind pub - !help helper
proc helper {n u h c t} {  
if {[channel get $c mysql-search] == 1 } {
putserv "PRIVMSG $c :To search the database the correct syntax is: !search <title>" 
}
}

#the public trigger -|- = anybody can use the trigger

bind pub -|- !search allscenesearch

proc allscenesearch {n u h c t} {
if {[channel get $c mysql-search] == 1 } {
regsub -all {\`|\"|'|\$|\'} $t {} t
regsub -all { |\*} $t {%} t
if {$t == ""} {
putquick "notice $n :-s <title>"
return
}
set db_search [mysqlconnect -host $::db(host) -user $::db(user) -password $::db(pass)]
mysqluse $db_search $::db(name)
set search [mysqlsel $db_search "SELECT $::t(tracknum),$::t(artist),$::t(title),$::t(path) FROM $::t(table) WHERE $::t(title) LIKE '%$t%' ORDER BY $::t(tracknum) DESC LIMIT 1" -flatlist]
mysqlclose $db_search
regsub -all {\{} $search "" search
regsub -all {\}} $search "" search
if { $search == "" } {
puthelp "PRIVMSG $c :No results matching *$search_id*"
return
} else {
puthelp "PRIVMSG $c :\(SEARCH RESULTS\) $search"
  }
 }
}

putlog "mysql_search.tcl by doggo #alt.binaries.inner-sanctum@efnet LOADED"

Code: Select all

#the table info


CREATE TABLE egg (
  tracknum int(15) NOT NULL,
  artist varchar(200) NOT NULL,
  title varchar(200) NOT NULL,
  path varchar(400) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



INSERT INTO egg (tracknum, artist, title, path) VALUES
(4444, 'script test 1', 'test 1', '/home/doggo/test 1.mp3'),
(1111, 'script test 2', 'test 2', '/home/doggo/test 2.mp3'),
(3333, 'script test 3', 'test 3', '/home/doggo/test 3.mp3');

Code: Select all

#working in the channel

[14:55] <doggo> !help
[14:55] <allscene> To search the database the correct syntax is: !search <title>
[14:55] <doggo> !search test 1
[14:55] <allscene> (SEARCH RESULTS) 4444 script test 1 test 1 /home/doggo/test 1.mp3
[14:55] <doggo> !search test 2
[14:55] <allscene> (SEARCH RESULTS) 1111 script test 2 test 2 /home/doggo/test 2.mp3
[14:55] <doggo> !search test 3
[14:55] <allscene> (SEARCH RESULTS) 3333 script test 3 test 3 /home/doggo/test 3.mp3
[14:55] <doggo> and when there are no results to display
[14:56] <doggo> !search test 4
[14:56] <allscene> No results matching *test%4*

hope this helps you out or at least helps you to figure it out, to write your own

[DJCharlie]

That's very nice code!

But that part of the script is working. What we need help with is the !fetch part.

Code: Select all

proc pub_fetch {nick host hand chan text} {
   global db
   set sqlhand [mysqlconnect -host $db(hostname) -user $db(username)
 -password $db(password)]
   mysqluse $sqlhand $db(database)
   foreach query [mysqlsel $sqlhand "SELECT * FROM music WHERE tracknum = '$text'"] {
      putlog "$query"
      putserv "NOTICE $nick :Sending you Song #[lindex $query 0] [lindex $query 1] - [lindex $query 2]"
   }
   mysqlclose $sqlhand
}

For some reason, every time it runs. $query returns a value of 1 instead of the track number.

[nml375]

It would seem you forgot the -list option with ::myqsl::sel...

Also, I took the liberty of adding some validation to the code, to protect against SQL-injection attacks... (I assume you've added the ::mysql::escape filter to your search-function already).

Code: Select all

proc pub_fetch {nick host hand chan text} {
  global db

  if {![string is int $text]} {
    puthelp "NOTICE $nick :$text is not a valid integer, please try again"
    return
  }

  set sqlhand [mysqlconnect -host $db(hostname) -user $db(username) -password $db(password)]
  mysqluse $sqlhand $db(database)

  set query "SELECT * FROM `music` WHERE `tracknum` = $text"
  putlog $query
  foreach record [mysqlsel $sqlhand $query -list] {
    putlog $record
    putserv "NOTICE $nick :Sending you Song #[lindex $record 0] [lindex $record 1] - [lindex $record 2]"
  }
  mysqlclose $sqlhand
}

[doggo]

LOL sorry i totally read wrong i thought you were still tryong to sort out the search....


i just had to change a few things but this works fine

Code: Select all

#how it looks in the db

4444 	script test 1 	test 1 	/home/doggo/test/test_1.mp3

Code: Select all

#how it looks in channel

[20:23] <doggo> !help
[20:23] <allscene> To fetch a file from the database the correct syntax to use is: !fetch <tracknum>
[20:23] <doggo> !fetch 4444
[20:23] <allscene> Sending file to doggo
[20:23] <doggo> !fetch 5555
[20:23] <allscene> No file to send for tracknumber *5555* doggo

Code: Select all

#what the bot says on the party line

[20:23] <allscene> [20:24] Begin DCC send test_1.mp3 to doggo
[20:23] <allscene> [20:24] Finished dcc send test_1.mp3 to doggo

Code: Select all

#the script modified

# Rls: #mysql_fetch.tcl
# Date: 24/07/10
# Coded by: doggo
# Contact: #alt.binaries.inner-sanctum@EFNET
############################################

package require mysqltcl 3.05

#channel flag    .chanset #YOUR_CHAN +mysql-fetch

setudef flag mysql-search

#connect to db

set db(host) "***********"
set db(user) "***********"	
set db(pass) "***********"
set db(name) "egghelp"

#table info

set t(tracknum) "tracknum"
set t(artist) "artist"
set t(title) "title"
set t(path) "path"

#from table

set t(table) "egg"

#output channel

set output_channel "#allscene"



###script starts###

# the help section 

bind pub - !help helper
proc helper {n u h c t} {  
if {[channel get $c mysql-fetch] == 1 } {
putserv "PRIVMSG $c :To fetch a file from the database the correct syntax to use is: !fetch <tracknum>" 
}
}

#the public trigger -|- = anybody can use the trigger

bind pub -|- !fetch fetch_file

proc fetch_file {n u h c t} {
if {[channel get $c mysql-fetch] == 1 } {
if {$t == ""} {
putquick "notice $n :!fetch <tracknum>"
return
}
set db_search [mysqlconnect -host $::db(host) -user $::db(user) -password $::db(pass)]
mysqluse $db_search $::db(name)
set search [mysqlsel $db_search "SELECT $::t(path) FROM $::t(table) WHERE $::t(tracknum) LIKE '%$t%' ORDER BY $::t(tracknum) DESC LIMIT 1" -flatlist]
mysqlclose $db_search
regsub -all {\{} $search "" search
regsub -all {\}} $search "" search
if { $search == "" } {
puthelp "PRIVMSG $c :No file to send for tracknumber *$t* $n"
return
} else {
              puthelp "PRIVMSG $c :Sending file to $n"

		dccsend $search $n
  }
 }
}

putlog "mysql_fetch.tcl by doggo #alt.binaries.inner-sanctum@efnet LOADED"

hope this helps

[nml375]

doggo,
Why on earth are you using all those regexp's?
Use proper list operations instead, since that's what mysqlsel returns with either -list or -flatlist. As for avoiding SQL-injections, as mentioned before, there's the mysqlescape function - it's faster and safer (uses mysql_real_escape_string from the MySQL C-API which honors charsets).

[doggo]

doggo,
Why on earth are you using all those regexp's?
Use proper list operations instead, since that's what mysqlsel returns with either -list or -flatlist. As for avoiding SQL-injections, as mentioned before, there's the mysqlescape function - it's faster and safer (uses mysql_real_escape_string from the MySQL C-API which

they were used in the search script to get rid of { } and to replace any spaces with a %

i just left them in there when i altered it to send the file from the path stored in the db i know its not the best code but

it works fine with no errors, yeah it could be better but at least i try to help

[DJCharlie]

It's working now! Thanks everyone!

Stop by the station (kjsr.net) next Friday around 7 eastern and I'll play some music for you!

[nml375]

Any {} would be there because it's a tcl-list.. using regular expressions like that will only break the list structure, and is a very bad practise...
Since it's a flatlist, just use lindex, which retrieves one list item...

That goes for your search-function as well...

[doggo]

so how should it look ?

my knowledge of regex and regsub is limited, and it got rid of the { } in the output of the result in channel

[nml375]

doggo,
Based on your script, something like this:

Code: Select all

proc fetch_file {n u h c t} {
  if {$t == ""} {
    putquick "notice $n :!fetch <tracknum>"
    return
  }

  if {[channel get $c mysql-fetch] == 1 } {

#Sanitize $t, avoiding any kind of SQL-injections.
    set t [mysqlescape $t]

#Replace * with %
#Using string map should be quicker than regsub 
    set t [string map {* %} $t]

    set db_search [mysqlconnect -host $::db(host) -user $::db(user) -password $::db(pass)]
    mysqluse $db_search $::db(name)
    set search [mysqlsel $db_search "SELECT $::t(path) FROM $::t(table) WHERE $::t(tracknum) LIKE '%$t%' ORDER BY $::t(tracknum) DESC LIMIT 1" -flatlist]
    mysqlclose $db_search

#Get the first entity from the result list
    set search [lindex $search 0]

    if { $search == "" } {
      puthelp "PRIVMSG $c :No file to send for tracknumber *$t* $n"
      return
    } else {
      puthelp "PRIVMSG $c :Sending file to $n"
      dccsend $search $n
    }
  }
}

Edit: Typo,

Code: Select all

mysqlescape $t

Should have been

Code: Select all

set t [mysqlescape $t]

Same with string map