Re: parse xml file, put results in mysql db

casper_the_ghost · November 16, 2006, 2:57pm

That works fine. Below is the db stuff - can anyone figure out what I’m

The last line is where it fails - if I put in values, it works fine, so
I know the error is in my variables. This is first time I"ve programmed
ruby, so I’m not sure what is wrong…

–
Posted via http://www.ruby-forum.com/.

Where are these variables being set? Could we have the whole code,
either
here or on pastebin.com for example?

My first instinct would be to remove the “#{}” around the variables,
though I
can’t see that being the main issue.

casper_the_ghost · November 16, 2006, 3:29pm

My first instinct would be to remove the “#{}” around the variables, though I
can’t see that being the main issue.

That sounds plausible.

Usually, I don’t use prepare and execute, but instead:

DBI.connect(‘DBI:ODBC:database’) do |dbh|
your_data_structure.each do |data|
sql = “INSERT INTO table
(col1,
col2,
col3)
VALUES
(’#{data.val1}’,
‘#{data.val2}’,
‘#{data.val3}’)”
dbh.do( sql )
end
end

casper_the_ghost · November 16, 2006, 3:50pm

Jon Egil S. wrote:

DBI.connect(‘DBI:ODBC:database’) do |dbh|
your_data_structure.each do |data|
sql = “INSERT INTO table
(col1,
col2,
col3)
VALUES
(‘#{data.val1}’,
‘#{data.val2}’,
‘#{data.val3}’)”
dbh.do( sql )
end
end

Pardon me, but your gaping SQL injection hole is showing.

casper_the_ghost · November 16, 2006, 6:35pm

Here’s the full code - I’m reading in nmap output in scanfile.xml and
want to put the data in a mysql db:

#! /usr/bin/ruby

require ‘rexml/document’
require “mysql”
require “dbi”
include REXML
scanfile = File.new(‘scanfile.xml’)
doc = Document.new(scanfile)
root = doc.root

doc.elements.each(“nmaprun”) { |element|
puts element.attributes[“args”]
args = element.attributes[“args”]
puts element.attributes[“startstr”]
timeofscan = element.attributes[“startstr”]
puts element.attributes[“version”]
version = element.attributes[“version”] }

doc.elements.each(“nmaprun/scaninfo”) { |element|
puts element.attributes[“type”]
scantype = element.attributes[“type”]
puts element.attributes[“protocol”]
protocol = element.attributes[“protocol”]
puts element.attributes[“numservices”]
numservices = element.attributes[“numservices”]

puts element.attributes[“services”]

    services = element.attributes["services"]   }

doc.elements.each(“nmaprun/scaninfo/host”) { |element|
puts element.attributes[“status state”] }

database insert

dbname=“nmap”
m = Mysql.new(“localhost”, “root”, “”)
dbh=DBI.connect(“dbi:Mysql:nmap:localhost”, “root”, “”)
m.select_db(dbname)
sth=dbh.prepare(“INSERT INTO rawdata
(file,tool,arguments,startime,version) VALUES (?,?,?,?,?)”)
sth.execute(“scanfile.xml”, “nmap”, “${args}”, “#{timeofscan}”,
“${version}”")

casper_the_ghost · November 16, 2006, 7:57pm

I don’t recognise the ${} syntax in the string interpolation, is this
just something I’ve not come across yet? Again, “#{variable}” is
basically an expensive way of saying variable, try it without them.

Additionally, what error are you getting?

casper_the_ghost · November 16, 2006, 7:08pm

Pardon me, but your gaping SQL injection hole is showing.

What’s a SQL Injection Bug? – Joel on Software

Obviously this is vulnerable for SQL-injections, so I thank you both for
completing the picture.

What I should have written was: “In my setting, where I massage
internally
structured and validated data between various formats, I prefer this for
readability.”

The people who could exploit this are the ones who have so much access
that they probably could do much worse maladies. Which brings us over to
the risk from unfaithful servants.

But I didn’t write that, and your comments are indeeed correct
and appreciated.

All the best
JE

casper_the_ghost · July 31, 2007, 10:19am

2006/11/16, Kathy S. [email protected]:

doc = Document.new(scanfile)
doc.elements.each(“nmaprun/scaninfo”) { |element|
puts element.attributes[“status state”] }

database insert

dbname=“nmap”
m = Mysql.new(“localhost”, “root”, “”)
dbh=DBI.connect(“dbi:Mysql:nmap:localhost”, “root”, “”)
m.select_db(dbname)
sth=dbh.prepare(“INSERT INTO rawdata
(file,tool,arguments,startime,version) VALUES (?,?,?,?,?)”)
sth.execute(“scanfile.xml”, “nmap”, “${args}”, “#{timeofscan}”,
“${version}”")

You have a scoping problem: you set timeofscan etc. inside the block
when traversing the XML document. But they are not visible outside
the block and thus you likely get a NameError.

If you want to insert multiple values into the DB you need to traverse
the XML doc and then execute for each iteration.

I’d also use the block forms of your DB connection methods and file
handling in order to make sure connections are properly closed etc.
It’s good to start that habit early.

Sample:

doc = File.open(‘scanfile.xml’, ‘rb’) {|scanfile| Document.new(scanfile)
}

Kind regards

robert

casper_the_ghost · July 31, 2007, 9:50am

hi, i try the same code. there is a problem in the last line. but i dont
understant why?

NameError: undefined local variable or method mobile_device_id' for RailsMaintainer:Class from ./script/../config/../config/../lib/rails_maintainer.rb:76:inimportContentDelivery’
from (irb):1

contents.elements.each("MobileDevices/MobileDevice") { |element|

puts element.attributes[“id”]
mobile_device_id = element.attributes[“id”] }

contents.elements.each("MobileDevices/MobileDevice") { |element|

puts element.attributes[“manufacturer”]
manufacturer = element.attributes[“manufacturer”] }

contents.elements.each("MobileDevices/MobileDevice") { |element|

puts element.attributes[“model”]
model = element.attributes[“model”] }

dbname="abo_development"
m = Mysql.new("localhost", "root", "")
dbh=DBI.connect("dbi:Mysql:abo_development:localhost", "root", "")
m.select_db(dbname)
sth=dbh.prepare("INSERT INTO mobile_devices
(mobile_device_id,manufacturer,model) VALUES (?,?,?)")
sth.execute("content.xml", "abo_development", "#{mobile_device_id}",

“#{manufacturer}”, “#{model}”)

casper_the_ghost · July 31, 2007, 3:22pm

Robert K. wrote:

2006/11/16, Kathy S. [email protected]:

doc = Document.new(scanfile)
doc.elements.each(“nmaprun/scaninfo”) { |element|
puts element.attributes[“status state”] }

database insert

dbname=“nmap”
m = Mysql.new(“localhost”, “root”, “”)
dbh=DBI.connect(“dbi:Mysql:nmap:localhost”, “root”, “”)
m.select_db(dbname)
sth=dbh.prepare(“INSERT INTO rawdata
(file,tool,arguments,startime,version) VALUES (?,?,?,?,?)”)
sth.execute(“scanfile.xml”, “nmap”, “${args}”, “#{timeofscan}”,
“${version}”")

You have a scoping problem: you set timeofscan etc. inside the block
when traversing the XML document. But they are not visible outside
the block and thus you likely get a NameError.

If you want to insert multiple values into the DB you need to traverse
the XML doc and then execute for each iteration.

I’d also use the block forms of your DB connection methods and file
handling in order to make sure connections are properly closed etc.
It’s good to start that habit early.

Sample:

doc = File.open(‘scanfile.xml’, ‘rb’) {|scanfile| Document.new(scanfile)
}

Kind regards

robert

Ok, thank you ! this way the code works:

model = {}
mobils.elements.each("MobileDevices/MobileDevice") { |element| puts

element.attributes[“model”]
model = element.attributes[“model”] }

manufacturer = {}
mobils.elements.each("MobileDevices/MobileDevice") { |element| puts

element.attributes[“manufacturer”]
manufacturer = element.attributes[“manufacturer”] }

mobil_device_id = {}
mobils.elements.each("MobileDevices/MobileDevice") { |element| puts

element.attributes[“id”]
mobil_device_id = element.attributes[“id”] }

# db insert
dbname="abo_development"
m = Mysql.new("localhost", "root", "")
dbh=DBI.connect("dbi:Mysql:abo_development:localhost", "root", "")
m.select_db(dbname)

sth=dbh.prepare("INSERT INTO mobile_devices (model, manufacturer,

mobil_device_id)
VALUES
(?,?,?)“)
sth.execute(”#{model}", “#{manufacturer}”, “#{mobil_device_id}”)

sth.finish
dbh.disconnect

But there is one problem: it writes only one row in the database. Can
anyone say why?

casper_the_ghost · July 31, 2007, 3:23pm

But there is one problem: it writes only one row in the database. Can
anyone say why?

casper_the_ghost · July 31, 2007, 4:43pm

2007/7/31, Hans M. [email protected]:

m.select_db(dbname)
the XML doc and then execute for each iteration.
Kind regards
sth.execute("#{model}", "#{manufacturer}", "#{mobil_device_id}")


sth.finish
dbh.disconnect
But there is one problem: it writes only one row in the database. Can
anyone say why?

Yes. You did not follow my directions. Think a moment about the
logic you are employing and it should be immediately clear why you
only get one record in the DB.

robert

casper_the_ghost · August 1, 2007, 10:12am

Robert K. wrote:

2007/7/31, Hans M. [email protected]:
m.select_db(dbname)
the XML doc and then execute for each iteration.
Kind regards
sth.execute("#{model}", "#{manufacturer}", "#{mobil_device_id}")


sth.finish
dbh.disconnect
But there is one problem: it writes only one row in the database. Can
anyone say why?
Yes. You did not follow my directions. Think a moment about the
logic you are employing and it should be immediately clear why you
only get one record in the DB.

robert

Ok, thanks. now i know why. my code looks this way now:

mobils = REXML::Document.new(f)
root = mobils.root

models = []
manufacturers = []
mobil_device_ids = []

mobils.elements.each("MobileDevices/MobileDevice") { |element| puts

element.attributes[“model”]
models.push element.attributes[“model”] }

mobils.elements.each("MobileDevices/MobileDevice") { |element| puts

element.attributes[“manufacturer”]
manufacturers.push element.attributes[“manufacturer”] }

mobils.elements.each("MobileDevices/MobileDevice") { |element| puts

element.attributes[“id”]
mobil_device_ids.push element.attributes[“id”] }

dbname="abo_development"
m = Mysql.new("localhost", "root", "")
dbh=DBI.connect("dbi:Mysql:abo_development:localhost", "root", "")
m.select_db(dbname)
sth=dbh.prepare("INSERT INTO mobil_devices (model, manufacturer,

mobil_device_id,)
VALUES (?,?,?)")
models.each_index do |index|
model = models[index]
manufacturer = manufacturers[index]
mobil_device_id = mobil_device_ids[index]
sth.execute(“mobils.xml”, “abo_development”, “#{model}”,
“#{manufacturer}”, “#{mobil_device_id}”)
end

but i get unfortunattely this failure message:
RuntimeError: Too many SQL parameters
from /usr/local/lib/ruby/site_ruby/1.8/dbi/sql.rb:192:in bind' from /usr/local/lib/ruby/site_ruby/1.8/DBD/Mysql/Mysql.rb:569:in execute’
from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:617:in execute' from ./script/../config/../config/../lib/rails_maintainer.rb:57:in importContentDelivery’
from
./script/…/config/…/config/…/lib/rails_maintainer.rb:53:in
each_index' from ./script/../config/../config/../lib/rails_maintainer.rb:53:in importContentDelivery’
from (irb):1

casper_the_ghost · August 1, 2007, 11:25am

On 1 Aug 2007, at 17:12, Hans M. wrote:

mobils.elements.each("MobileDevices/MobileDevice") { |element|  
puts
models.each_index do |index|
from /usr/local/lib/ruby/site_ruby/1.8/dbi/sql.rb:192:in
each_index' from ./script/../config/../config/../lib/rails_maintainer.rb:53:inimportContentDelivery’
from (irb):1

This line:

sth=dbh.prepare("INSERT INTO mobil_devices (model, manufacturer,
mobil_device_id,)
VALUES (?,?,?)")

prepares an SQL statement expecting three values (model
manufacturer,mobil_device_id).

This line:

    sth.execute("mobils.xml", "abo_development", "#{model}",
“#{manufacturer}”, “#{mobil_device_id}”)

attempts to execute the SQL with 5 values (“mobils.xml”,
“abo_development”, model, manufacturer, mobil_device_id). Hence the
complaint:

RuntimeError: Too many SQL parameters

Alex G.

Bioinformatics Center
Kyoto University

casper_the_ghost · August 1, 2007, 12:01pm

Alex G. wrote:

On 1 Aug 2007, at 17:12, Hans M. wrote:
mobils.elements.each("MobileDevices/MobileDevice") { |element|  
puts
models.each_index do |index|
from /usr/local/lib/ruby/site_ruby/1.8/dbi/sql.rb:192:in
each_index' from ./script/../config/../config/../lib/rails_maintainer.rb:53:inimportContentDelivery’
from (irb):1
This line:
sth=dbh.prepare("INSERT INTO mobil_devices (model, manufacturer,
mobil_device_id,)
VALUES (?,?,?)")
prepares an SQL statement expecting three values (model
manufacturer,mobil_device_id).

This line:
    sth.execute("mobils.xml", "abo_development", "#{model}",
“#{manufacturer}”, “#{mobil_device_id}”)
attempts to execute the SQL with 5 values (“mobils.xml”,
“abo_development”, model, manufacturer, mobil_device_id). Hence the
complaint:

RuntimeError: Too many SQL parameters

Alex G.

Bioinformatics Center
Kyoto University

Ok, thank you. I found the failure, but get a new failure:

DBI::DatabaseError: 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 ‘)
VALUES (‘735i’,‘Alcatel’,‘1133’)’ at line 1
from /usr/local/lib/ruby/site_ruby/1.8/DBD/Mysql/Mysql.rb:115:in
error' from /usr/local/lib/ruby/site_ruby/1.8/DBD/Mysql/Mysql.rb:579:inexecute’
from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:617:in execute' from ./script/../config/../config/../lib/rails_maintainer.rb:58:inimportContentDelivery’
from
./script/…/config/…/config/…/lib/rails_maintainer.rb:54:in
each_index' from ./script/../config/../config/../lib/rails_maintainer.rb:54:inimportContentDelivery’
from (irb):2

casper_the_ghost · August 2, 2007, 2:08am

On 1 Aug 2007, at 19:01, Hans M. wrote:

Ok, thank you. I found the failure, but get a new failure:

DBI::DatabaseError: 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 ‘)
VALUES (‘735i’,‘Alcatel’,‘1133’)’ at line 1

The error message tells you what to do. Check the syntax of your SQL.
You have:

“INSERT INTO mobil_devices (model, manufacturer,mobil_device_id,)
VALUES (?,?,?)”

Can you see the error? It is just where the error message tells you
it is. You have an extra ‘,’ in your column list.

Alex G.

Bioinformatics Center
Kyoto University