Parse xml file, put results in mysql db

ruby-talk
1

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.

···

--- 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-forum.com/\.

2

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

···

--
Jon Egil Strand
Phone: +47 98232340
jes@luretanker.no

3

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.

···

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

4

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-forum.com/.

5

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

···

--
Jon Egil Strand
Phone: +47 98232340
jes@luretanker.no

6

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-forum.com/\.

7

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. :slight_smile:

Sample:

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

Kind regards

robert

···

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

8

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-forum.com/.

9

Robert Klemme wrote:

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. :slight_smile:

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?

···

2006/11/16, Kathy Simmons <kathys39@hotmail.com>:

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

10

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

···

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

11

Yes. You did not follow my directions. :slight_smile: 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

···

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. :slight_smile:
>
> 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?

12

Robert Klemme wrote:

>> 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. :slight_smile: 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

···

2007/7/31, Hans Meier <mailboxoderso@gmx.net>:

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

13

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

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 Gutteridge

Bioinformatics Center
Kyoto University

···

On 1 Aug 2007, at 17:12, Hans Meier wrote:

14

Alex Gutteridge 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:in
`importContentDelivery'
        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 Gutteridge

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:in
`execute'
        from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:617:in `execute'
        from
./script/../config/../config/../lib/rails_maintainer.rb:58:in
`importContentDelivery'
        from
./script/../config/../config/../lib/rails_maintainer.rb:54:in
`each_index'
        from
./script/../config/../config/../lib/rails_maintainer.rb:54:in
`importContentDelivery'
        from (irb):2

···

On 1 Aug 2007, at 17:12, Hans Meier wrote:

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

15

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 Gutteridge

Bioinformatics Center
Kyoto University

···

On 1 Aug 2007, at 19:01, Hans Meier wrote: