Asp Forum - Re: parse xml file, put results in mysql db

Sebastian Reid

11/16/2006 1:57:00 PM

--- Kathy Simmons <kathys39@hotmail.com> wrote:
> I'm a ruby newbie, parsing an xml file and putting results in mysql
> file. Working on linux. I can parse the data and print it out ok.
> Problem comes when I try to insert it into the mysql db - I get an error
> for the variables.
>
> Here's how I'm grabbing the data from xml and assigning to variable:
> doc.elements.each("elementname") { |element| puts
> element.attributes["args"]
> args = element.attributes["args"}.......
>
> That works fine. Below is the db stuff - can anyone figure out what I'm
> doing wrong?
>
> dbname = "data"
> m = Mysql.new("localhost", "userid", "passwd")
> dbh = DBI.connect("dbi:Mysql:data:localhost", "userid", "passwd")
> m.select_db(dbname)
> sth=dbh.prepare("INSERT INTO rawdata (file, tool, arguments) VALUES
> (?,?,?)")
> sth.execute("#{filename}", "#{toolname}", "#{args}" )
>
>
> 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-....
>
>

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.

14 Answers

Jon Egil Strand

11/16/2006 2:30:00 PM

Mike Fletcher

11/16/2006 2:51:00 PM

Jon Egil Strand 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.

http://joelonsoftware.com/items/2006/...

--
Posted via http://www.ruby-....

Kathy Simmons

11/16/2006 5:36:00 PM

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}"")

--
Posted via http://www.ruby-....

Jon Egil Strand

11/16/2006 6:07:00 PM

Sebastian Reid

11/16/2006 6:56:00 PM

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?

On 16 Nov 2006, at 17:35, Kathy Simmons wrote:

> 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}"")
>
>
>
> --
> Posted via http://www.ruby-....
>
>

Hans Meier

7/31/2007 7:50:00 AM

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:in
`importContentDelivery'
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}")

--
Posted via http://www.ruby-....

Robert Klemme

7/31/2007 8:19:00 AM

2006/11/16, Kathy Simmons <kathys39@hotmail.com>:
> 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}"")

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

Hans Meier

7/31/2007 1:24:00 PM

Robert Klemme wrote:
> 2006/11/16, Kathy Simmons <kathys39@hotmail.com>:
>> 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?
--
Posted via http://www.ruby-....

Hans Meier

7/31/2007 1:24:00 PM

But there is one problem: it writes only one row in the database. Can
anyone say why?
--
Posted via http://www.ruby-....

Robert Klemme

7/31/2007 2:43:00 PM

2007/7/31, Hans Meier <mailboxoderso@gmx.net>:
> Robert Klemme wrote:
> > 2006/11/16, Kathy Simmons <kathys39@hotmail.com>:
> >> 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?

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

comp.lang.ruby

Re: parse xml file, put results in mysql db

Sebastian Reid

Jon Egil Strand

Mike Fletcher

Kathy Simmons

Jon Egil Strand

Sebastian Reid

Hans Meier

Robert Klemme

Hans Meier

Hans Meier

Robert Klemme

x Login to ForumsZone