Raw SQL Insert into remote ruby on rails database

Question

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'
);

mu is too short · Accepted Answer · 2013-03-26 04:16:07Z

3

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.

answered Mar 26, 2013 at 4:16

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Austio Over a year ago

Thanks, i did this and it! For those of you that run into this, attempt to use mu is too shorts solution and still get a problem. Notice that the last parts are actually variables and the only stringed part is the insert into. That got me at first becuase i wasn't paying attention

mu is too short Over a year ago

@Austio: Is there anything I need to clarify or was the misunderstanding just a side effect of the double quoted string being a bit long?

Austio Over a year ago

It was totally a misreading on my part (and a mistyping in the response above) When i originally used your response it was in the form of @db.execute("insert into #{@table} (case_number) values (?) [ {#self.case_number.to_blob} ... ])" Notice that i missed the comma that would tell SQLite3 the correct way to do it. After i looked at what you wrote above more closely i realized that I missed the , which makes the second part a new parameter. It was correct the way you wrote it, i just missed that it was 2 parameters

Jarod Elliott · Accepted Answer · 2013-03-26 04:14:08Z

0

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.

edited Mar 26, 2013 at 4:14

answered Mar 26, 2013 at 4:07

Jarod Elliott

15.7k3 gold badges37 silver badges34 bronze badges

Comments

Stuart M · Accepted Answer · 2013-03-26 04:07:41Z

-1

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.

answered Mar 26, 2013 at 4:07

Stuart M

11.6k6 gold badges47 silver badges59 bronze badges

Collectives™ on Stack Overflow

Raw SQL Insert into remote ruby on rails database

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related