CODEWITHSUNDEEP
ruby-on-railsrubydatabasedatabase-connection
Austio
Austio

Reputation: 6085

Raw SQL Insert into remote ruby on rails database

I am having trouble with inserting data into an sqlite development database.

My app has 2 servers, one that scrapes browsers (browserscraper) and another that serves client requests. Each of these have a production and development.

I'm setting up development to insert the final scraped data into my development client request server however I can't get the insert to work. I suspect it is related to escaping the content properly but i have been on google for several hours trying to figure this out.

Here is the insert going from my scraping app to my remote client app

@sql_insert = "INSERT INTO #{@table} (`case_number`, `style_of_case`, `circuit`, `judge`, `location`, `disposition`, `date_filed`, `disposition_date`, `case_type`, 'lead_details', 'charge_details')"

@sql_values = " VALUES (#{self.case_number.to_blob}, #{self.style_of_case.to_blob}, #{self.circuit.to_blob}, #{self.judge.to_blob}, #{self.location.to_blob}, #{self.disposition.to_blob}, #{self.date_filed.to_blob}, #{self.disposition_date.to_blob}, #{self.case_type.to_blob},  #{self.lead_details.to_blob}, #{self.charge_details.to_blob});"

@db = SQLite3::Database::new('E:/Sites/aws/db/development.sqlite3')
@db.execute(@sql_insert + @sql_values + "COMMIT;")

The ultimate query looks something like this (quite ugly i know). The last two that i am inserting are yaml

INSERT INTO lead_to_processes (`case_number`, `style_of_case`, `circuit`, `judge`, `location`, `disposition`, `date_filed`, `disposition_date`, `case_type`, 'lead_details', 'charge_details') VALUES (130025129, 130025129 - CITY, 1st(Jim, Counties), LOVEKAMP, KELLY LAREE, Schuyler, Plea Written, 03/19/2012, 03/19/201, Municipal Ordinance - Traffic,  ---
1-address_line_1: 6150 RICHLAND RD
1-address_line_2: ''
1-city: 'GEORGIA'
1-birth_year: '1955' 
1-is_alive: 1
, ---
1-Description: Not Available }
1-Code: '95220'
);

Upvotes: 1

Views: 1526

Answers (3)

mu is too short
mu is too short

Reputation: 434685

You're not hacking PHP in 1999 so you shouldn't be using string interpolation to talk to your database. SQLite3::Database#execute supports placeholders, please use them; your execute should look something like this:

@db.execute("insert into #{@table} (case_number, style_of_case, ...) values (?, ?, ...)", [
    self.case_number.to_blob,
    self.style_of_case.to_blob,
    ...
])

That way the database interface will take care of all the quoting and escaping and whatnot for you.

Upvotes: 3

Jarod Elliott
Jarod Elliott

Reputation: 15670

I'm not familiar with Ruby or SQLite, but purely looking at your query you have the last two column names quoted incorrectly with single quotes. 'lead_details' and 'charge_details' should not need to be in quotes unless you use back ticks like the other column names.

Further to that, the values you are inserting are not quoted correctly either. Most languages provide a function to escape and quote database strings appropriately.

I would also suggest checking what the actual error message from your insert is as it should help point you towards the problem in situations like this.

Upvotes: 0

Stuart M
Stuart M

Reputation: 11588

INSERT INTO lead_to_processes (case_number, style_of_case, circuit, judge, location, disposition, date_filed, disposition_date, case_type, 'lead_details', 'charge_details') VALUES (130025129, 130025129 - CITY, 1st(Jim, Counties), LOVEKAMP, KELLY LAREE, Schuyler, Plea Written, 03/19/2012, 03/19/201, Municipal Ordinance - Traffic, --- 1-address_line_1: 6150 RICHLAND RD 1-address_line_2: '' 1-city: 'GEORGIA' 1-birth_year: '1955' 1-is_alive: 1 , --- 1-Description: Not Available } 1-Code: '95220' );

It looks like, starting with 130025129 - CITY, your input values are not surrounded with quotes, so the query parser cannot parse it. I would surround each string value with single quotes.

Upvotes: -1

Related Questions