Ruby--construct a insert query with each_row, fetch_hash or others

Another try:

#!/usr/bin/ruby -w

require 'dbi'
require 'mysql'

numrows=0

print("Enter the number of multiplier value to generate rows: ");
numrows = gets.chomp.to_i

# Connect to the MySQL server sampe on appserver
     db=DBI.connect("DBI:Mysql:sampe:localhost","root","testadmin")
     db["AutoCommit"]=true

# select all the ipfrom values from the ip2locsector table
 
   res=db.execute("SELECT cvss, signature, type, classID, port FROM classification ORDER BY classID DESC")
   insert_stmt = "INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport) VALUES ("
        res.fetch_hash do |row|
        insert_stmt << "current_timestamp,UNIX_TIMESTAMP(),(#{row["cvss"]},#{row["signature"]},#{row["type"]},#{row["classID"]},#{row["port"]})"
                 insert_stmt << ")"    
                     
             puts insert_stmt
           db.execute(insert_stmt)
 end

RESULT****************************************************************************************************
This results in the insert statement with no quotes around the strings....therefore invalid insert statement...
I can see I can use that trailing redirect to complete the statement...

Make sure you use the code option so we get the formatting.  Easier to read!

Single quotes in your insert_stmt won't be interpolated:

insert_stmt << "current_timestamp,UNIX_TIMESTAMP(), '(#{row["cvss"]}' ..."

Select allOpen in new window

WesG

I dont see whats wrong with this......its prints correctly..but only inserts one then throws sql exception
******************************************************************************8
# select all the ipfrom values from the ip2locsector table
 
   res=db.execute("SELECT cvss, signature, type, classID, port FROM classification ORDER BY classID DESC")
   insert_stmt = "INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES ("
        res.fetch_hash do |row|
         ip_val=row.first
            numrows.times do
        insert_stmt << "current_timestamp,UNIX_TIMESTAMP(),#{row["cvss"]},'#{row["signature"]}','#{row["type"]}',#{row["classID"]},#{row["port"]}"
        insert_stmt << ",(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()\n"    
           print("#{ insert_stmt}\n")
           db.execute(insert_stmt)
         end  
         end
*****************************************************************************

also which would be faster...fetch_array/hash, each.xxx or something else...

sorry I didnt use the code option again...my bad i will next time

What's the exception?  Kinda hard for me to know if you don't tell me!

Also, paste the print()ed out SQL before it gets executed so I can see that.

As for speed, my guess is that mysql is the limiting factor, not ruby.  We can do some EXPLAIN checking after we get it working.

INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES (current_timestamp,UNIX_TIMESTAMP
(),0,'patch software allowing exfiltration','Response',144,22,(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips
where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()

INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES (current_timestamp,UNIX_TIMESTAMP
(),0,'patch software allowing exfiltration','Response',144,22,(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips
where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()
current_timestamp,UNIX_TIMESTAMP(),0,'delete suspicious code files','Response',143,21,(select port from classification where 1=1 Order by RAND() limit
 1),(select ip from ips where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TI
MESTAMP()

Error code: 1064
Error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
current_timestamp,UNIX_TIMESTAMP(),0,'delete suspicious code files','Response',1' at line 2
error SQLSTATE: 42000

Okay, looks like you're missing a quote:
'Response',1'
... should be:
'Response', '1'
Or:
'Response', 1

Yes but it shouldn't be putting a quote there at all no?

I dunno if you have that field set there as a number or a string.

It could be in the source data, too.  

I'm not sure what changes you've made to the code, but if you'll post the latest I'll check it out.

Ok here is the code that generated that...what i dont understand is it prints the first statement correctly ..the second row should be the same...thanks for help

#!/usr/bin/ruby -w

require 'dbi'
require 'mysql'

begin
  numrows=0

  print("Enter the multiplier value to generate rows: ");
  numrows = gets.chomp.to_i

# Connect to the MySQL server sampe on appserver
     db=DBI.connect("DBI:Mysql:sampe:localhost","root","pwd")
     db["AutoCommit"]=true

# select all the ipfrom values from the ip2locsector table
 
   res=db.execute("SELECT cvss, signature, type, classID, port   FROM classification ORDER BY classID DESC") 
   insert_stmt = "INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES ("
        res.fetch_hash do |row| 
	ip_val=row.first
	numrows.times do
	insert_stmt << "current_timestamp,UNIX_TIMESTAMP(),#{row["cvss"]},'#{row["signature"]}','#{row["type"]}',#{row["classID"]},#{row["port"]}"
        insert_stmt << ",(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()\n"	
           print("#{ insert_stmt}\n")
		   db.execute(insert_stmt)
	   end  
         end
rescue DBI::DatabaseError => e
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
puts "error SQLSTATE: #{e.state}"
ensure
db.disconnect if db  

end

Select allOpen in new window

Okay, looks like I was wrong.  That ending quote is the one from the 'error near' message.

Can you paste the entire SQL string that's being output before it's being run?  You said the first one works but the second doesn't, so paste both of them in and let's see what's different.

that first sql is inserted into the database..now I notice the next insert statement is the same as the first...so it isnt looping through the data..however since I have that on duplicate key at the end it should still insert into database..this is filler data...

Enter the multiplier value to generate rows: 35
INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES (current_timestamp,UNIX_TIMESTAMP
(),0,'patch software allowing exfiltration','Response',144,22,(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips
where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()

INSERT INTO securityEvents(reportDate,ID,CVSS,vulnerability,eventtype,classification,sport,dport,ipfrom,ipto) VALUES (current_timestamp,UNIX_TIMESTAMP
(),0,'patch software allowing exfiltration','Response',144,22,(select port from classification where 1=1 Order by RAND() limit 1),(select ip from ips
where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID = UNIX_TIMESTAMP()
current_timestamp,UNIX_TIMESTAMP(),0,'patch software allowing exfiltration','Response',144,22,(select port from classification where 1=1 Order by RAND
() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1),(select ip from ips where 1=1 Order by RAND() limit 1))ON DUPLICATE KEY UPDATE ID =
 UNIX_TIMESTAMP()

Error code: 1064
Error message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
current_timestamp,UNIX_TIMESTAMP(),0,'patch software allowing exfiltration','Res' at line 2
error SQLSTATE: 42000

Select allOpen in new window

OMG...how could i miss that....thanks....:-)

Meh. I missed it a couple times, too, right?  No worries.