1

Following on from;

Python - Reading BLOB type from SQLite3 DB

Python - Converting Hex to INT/CHAR

I have a script that now queries a sqlite3 database with a given id. From this id it finds the IP of that device. This is all done within table1. It then takes this found IP and goes to table2.

Within table2 it then tries to do a search for rows which have the field IP that matches the previously discovered IP.

Thanks to help in the previous questions I have managed to convert the original blob type that the IP is stored in the sqlite3 database into a readable format (into hex and then into an actual IP). Now I have found that I can't actually do an sql where command to then search for this. Perhaps naively I tried to do a search for the original return value from my first search however this returns a buffer

(<read-write buffer ptr 0x7f251c86d778, size 4 at 0x7f251c86d738>,)

This is something I can't obviously use to search for an exact copy of the blob as it contains other information. The only solution I have thought of is to traverse the db and convert each IP from a blob into hex and do a comparison against my previously stored variable where it has been converted into hex from the previous table.

Is this the only way I can perform a search like this? I have enjoyed a lot of help on this today so I am happy for pointers in the right direction

Code Excerpt: This returns the IP from the first table

rows = c.execute('select ip from table1 where name= ' + "\"" + host + "\"") 
   for row in rows:
       print str(row[0]).encode("hex")
Community
  • 1
  • 1
Draineh
  • 599
  • 3
  • 11
  • 28
  • Why are do you have to search inside blob data? Can't you store data you're after in proper columns? That is—deserialize it before storing it inside the database and come up with a real schema for that table? – patrys Sep 29 '11 at 15:23
  • the database I am accessing isn't one created by me, its created and used by an external service but I am trying to create a method of querying it as it does hold some useful information - sadly this means I have to work around however its been defined – Draineh Sep 29 '11 at 15:24
  • 1
    Not familiar with blob handling in sqlite but can't you `execute('SELECT bar FROM baz WHERE your_blob_field LIKE ?', '%foo%')`? – patrys Sep 29 '11 at 15:35
  • @patrys That doesn't work. I think its to do with the fact that python stores the retrieved blob data into a buffer which is then not the same as the original blob data in the DB. So when comparing the buffer to the blob it will never match – Draineh Sep 30 '11 at 08:23

0 Answers0