1

I have the following array of strings:

array = [id, message, date, length]

Example:

array = ["1", "test's message", "2016-01-01", "15"]

I want to merge it into one string. I will use that string to insert data in DB using:

ActiveRecord::Base.connection.execute

I did:

result = "(#{array[0}', '#{array[1]}', '#{array[2]}', '#{array[3]}')"

message contains special characters ' (ASCII code 039). This results in SQL exception. How can I construct a result string that includes the ' character?

EDIT:

To put data in BD I use:

conn = ActiveRecord::Base.connection
sql = "INSERT INTO table_name (`id`, `message`, `date`, `length`) VALUES #{result}"
  conn.execute sql

EDIT:

I fixed this using AR method:

ActiveRecord::Base.connection.quote(message)
dewastator
  • 213
  • 4
  • 13
  • 2
    You are doing it wrong. There is 2016 around an no one sane developer would construct SQL by joining strings. – Aleksei Matiushkin Jul 18 '16 at 07:18
  • At which point is the exception raised? During loading the file? During generation of the string? During SQL query? – sawa Jul 18 '16 at 07:19
  • `You are doing it wrong. There is 2016 around an no one sane developer would construct SQL by joining strings.` Why? I need performance. I want to put data in DB using raw SQL. I don't need AR because I have validated data and I am not scared about Dependency injection because it is simple backgroud job. Tell my why. I have huge amount of data and using plain sql increases inserting 20x – dewastator Jul 18 '16 at 07:20
  • `At which point is the exception raised? During loading the file? During generation of the string? During SQL query?` exception is rised because I have `'` char in message string. This is why I am asking how merge strings with special characters. You don't need exception message. – dewastator Jul 18 '16 at 07:23
  • Cannot be reproduced. The error is not raised because there is a `'` in the message string. – sawa Jul 18 '16 at 07:24
  • I edited message maybe this make it more clear. And yes error is raised because there is special character – dewastator Jul 18 '16 at 07:29
  • No it is not because of that. – sawa Jul 18 '16 at 07:40

4 Answers4

2

answer is here:

How to insert a value that contains an apostrophe (single quote)?

You basically double up the apostrophe: "test''s message"

Community
  • 1
  • 1
seph
  • 6,066
  • 3
  • 21
  • 19
1

You can use this code for generate result.

array = ["1", "test's message", "2016-01-01", "15"]

result = "(#{array.map{|s| s.gsub!("'", "''"); "'#{s}'"}.join(",")})"
# => => "('1','test''s message','2016-01-01','15')"
Lukas Baliak
  • 2,849
  • 2
  • 23
  • 26
0

You need to escape the special character like this

array = ["1", "test''s message", "2016-01-01", "15"]
Awais Shafqat
  • 546
  • 6
  • 13
-2

Use Array#join Doc Link

arr = [id, message, data, length]
join_result = arr.join(',')
spidergears
  • 194
  • 11