hpc and recall not importing

[zdavatz]

1. HPC and RECALL jobs are currently not importing.
2. URLs of Swissmedic seem to be the same.
3. DB Backup is here: http://sl_errors.oddb.org/db/22:00-postgresql_database-ch_oddb-backup.bz2
4. App is restarting constantly. Error seems to be: https://github.com/zdavatz/oddb.org/issues/275#issuecomment-2221425403
5. Is there some faulty data in this Swissmedic Dump?
6. https://1drv.ms/u/c/8db8718f73b2d606/EY7NTixa9RRFoOREewCxZBoBKDI2dmTIMlBv_GtMwDsw7A?e=xLveiD

[zdavatz]

1. Ok, it seems that Swissmedic it blocking our IP ;/.
2. This works on my local computer ruby -rnet/http -e 'url = URI("https://www.swissmedic.ch/swissmedic/de/home/humanarzneimittel/marktueberwachung/health-professional-communication--hpc-.html"); http = Net::HTTP.new(url.host, url.port); http.use_ssl = true; response = http.get(url); puts "Code: #{response.code}, Message: #{response.message} "'
3. ruby -rnet/http -e 'url = URI("https://www.swissmedic.ch/swissmedic/en/home/humanarzneimittel/market-surveillance/qualitaetsmaengel-und-chargenrueckrufe/batch-recalls.html"); http = Net::HTTP.new(url.host, url.port); http.use_ssl = true; response = http.get(url); puts "Code: #{response.code}, Message: #{response.message} "'

[zdavatz]

Related to #286

[zdavatz]

Ok, so somehow the HPC/RECALL updater - when running jobs/import_daily gets a connections refused, and then ODDB can not start anymore ;) and we get buffering.rb:214:insysread_nonblock'` error seen in #286

So the way HPC/RECALL Parser tries to connect with Swissmedic really upsets the connection of ODDB to Postgres.

[zdavatz]

1. Ok, it seems like Swissmedic has a self-signed certificate.

   
   require 'openssl'
   require 'socket'

Create an SSL context

context = OpenSSL::SSL::SSLContext.new context.verify_mode = OpenSSL::SSL::VERIFY_PEER # Verify the server's SSL certificate

Create a TCP socket and wrap it in SSL

socket = OpenSSL::SSL::SSLSocket.new(TCPSocket.new('swissmedic.ch', 443), context)

Establish the SSL connection

socket.connect

Send the HTTP GET request

socket.write("GET / HTTP/1.1\r\nHost: swissmedic.ch\r\n\r\n")

Read the server's response

response = socket.read

Print the response

puts response

Close the SSL connection

socket.close

results in

buffers.rb:12:in connect': SSL_connect returned=1 errno=0 peeraddr=162.23.129.86:443 state=error: certificate verify failed (self-signed certificate in certificate chain) (OpenSSL::SSL::SSLError) from buffers.rb:12:in

'

[zdavatz]

The solution seems to be: https.verify_mode = OpenSSL::SSL::VERIFY_NONE for the job update_swissmedic_feed

[zdavatz]

The last certificate seems to be self-signed!

require 'openssl'
require 'socket'

begin
  # Create an SSL context
  context = OpenSSL::SSL::SSLContext.new
  context.verify_mode = OpenSSL::SSL::VERIFY_NONE  # Disable certificate verification for this check

  # Create a TCP socket and wrap it in SSL
  socket = OpenSSL::SSL::SSLSocket.new(TCPSocket.new('swissmedic.ch', 443), context)

  # Establish the SSL connection
  socket.connect

  # Get the peer certificate chain
  cert_chain = socket.peer_cert_chain

  # Iterate through the certificate chain and check each certificate
  cert_chain.each_with_index do |cert, index|
    puts "Certificate #{index + 1}:"
    puts "Issuer: #{cert.issuer}"
    puts "Subject: #{cert.subject}"
    puts "Version: #{cert.version}"
    puts "Serial Number: #{cert.serial}"
    puts "Not Before: #{cert.not_before}"
    puts "Not After: #{cert.not_after}"
    puts "Signature Algorithm: #{cert.signature_algorithm}"
    puts "Public Key: #{cert.public_key}"

    # Check if the certificate is self-signed
    if cert.issuer == cert.subject
      puts "The certificate is self-signed."
    else
      puts "The certificate is not self-signed."
    end
    puts "\n"
  end

rescue => e
  puts "An error occurred: #{e.message}"
ensure
  # Close the SSL connection
  socket.close if socket && !socket.closed?
end

[zdavatz]

Certificate 3: Issuer: /C=BM/O=QuoVadis Limited/CN=QuoVadis Root CA 2 G3 Subject: /C=BM/O=QuoVadis Limited/CN=QuoVadis Root CA 2 G3 Version: 2 Serial Number: 390156079458959257446133169266079962026824725800 Not Before: 2012-01-12 18:59:32 UTC Not After: 2042-01-12 18:59:32 UTC Signature Algorithm: sha256WithRSAEncryption Public Key: -----BEGIN PUBLIC KEY----- MIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAoa4lsgEY3FeIP0br+a/i 6yNx4prRYWYhX6qvJ1HlbhsW1C19ULBTd714OmDiZAKbfIab1hqOrf8fFX/VlR4S y+YUhATB3zazFp+K48nbmDTO2DMXKEb8p8nw0rTVTQlySfnyh+Op2n2hfWuyOiWp bVJErPi+bvvcpnORkGGmAxQg8ueHo4itraCM/6YLJVIl5xYB1cu4NYEMozvw4eH8 Wl3OgHFt+EmrPju6uNeAAful61uzxV5gKjGgrzfoIDqfqDIsDMwJHdOejl28TJju xRpoe+xTpukUNaPfzYCfDEj7HPTxv0q4+tWMcUrHH63+QZqzg13yhFbvpVdDzimt jKtVv8T7WwHdIyGhWACOw9BqE+0T4xIrgNxn5pWyzR4ibir4QdTyyhQHjYpVEsZp 9biGaC9TXrDSqiHBmOYw42dVx5turBmoVaZFBtAjOtvrZV0qERHwO0/KbfQ0xHHk /wBa9lyuI2CFc/HkELElrtWSuxPBDOA52rQ5V7WrNapyITuDNecx33ohbrgyCH0d MpEVSmJyz+N3obzVERt2AWcI4EELw+sVbvikGdmiq6/iJ1JWKwKKLBQk+b9CAr8m yMaP4G44fVMt5e2Ys5VjaH/5NfTfiMVgNZLAfGkcYZUW0OveC68+BBBFZVhQOK9I 8lm2FvI8DZACxnAuAa08FdcCAwEAAQ== -----END PUBLIC KEY----- The certificate is self-signed.

[zdavatz]

1. we may also just have to update the net-protocol gem.

   
   Plugin: ODDB::RssPlugin

Error: Net::OpenTimeout Message: Net::OpenTimeout Backtrace: /home/zdavatz/.rbenv/versions/3.2.1/lib/ruby/gems/3.2.0/gems/net-protocol-0.1.2/lib/net/protocol.rb:43:in ssl_socket_connect' /home/zdavatz/.rbenv/versions/3.2.1/lib/ruby/3.2.0/net/http.rb:1342:inconnect'

[zdavatz]

Could this work in src/plugin/plugin.rb?

def fetch_with_http(url, retries = 3)
  uri = URI.parse(url)

  http = Net::HTTP.new(uri.host, uri.port)
  http.use_ssl = (uri.scheme == "https")
  http.read_timeout = 20
  http.open_timeout = 20

  request = Net::HTTP::Get.new(uri.request_uri)

  begin
    response = http.request(request)
    case response
    when Net::HTTPSuccess
      return response.body
    else
      raise "HTTP request failed with response code #{response.code}"
    end
  rescue Net::OpenTimeout, Net::ReadTimeout => e
    retries -= 1
    if retries > 0
      sleep(2)
      retry
    else
      raise e
    end
  end
end

[zdavatz]

This is working again. Swissmedic seems to have updated something.