01.02.2010

24799

SQL Select Blob

Wir haben eine Datenbank-Tabelle mit BLOBs. In diesen sind Dokumente (wie PDF) gespeichert. Für bestimmte Zugriffe sollen diese Dokumente verschlüsselt werden. Wir haben eine Verschlüsselung im Einsatz, nur muss ich prüfen, ob tatsächlich alle Dokumente entsprechend verfremdet wurden. Daher möchte ich gerne eine SQL-Abfrage starten, die mir anzeigt, ob eventuell noch unverfremdete Dokumente in der Tabelle vorliegen.

Hallo mal wieder,

wie gesagt:
- eine Tabelle mit BLOBs (Microsoft SQL Server 2005 auf Microsoft Windows Server 2003)
- in den Feldern PDFs, DOCs, XLS, HTML

Dateien haben ja bestimmte Dateiheader (PDF-Dateien beginnen immer mit "%pdf"), daher würde ich gerne die Tabelle nach diesen Dateiheadern durchsuchen.

Wie würde eine solche Abfrage aussehen? Kann ich nach diesem Dateiheader (wie "%pdf") suchen?
Oder kann ich nach dem entsprechenden HEX-Wert dieses Dateiheaders suchen (2550)?

Anbei Bild eines BLOBs/PDF.

Viele Grüße an alle anderen IT-Crowds,

Kaffeepause

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 134865

Url: https://administrator.de/contentid/134865

Ausgedruckt am: 05.11.2024 um 18:11 Uhr

13 Kommentare

Neuester Kommentar

Moin Kaffeepause,

eine undankbare Aufgabe hast du dir da aufhalsen lassen... macht (vergleichsweise) viel Aufwand mit einen vermuteten Nutzen von nahezu 0.
Denn die Erwartungshaltung ist ja sicherlich "Ja, es hat sich bestätigt, dass alle PDF-Dokumente zumindest nicht mehr mit jedem PDF-Reader einfach angezeigt werden können".

Also. der MSSQL-Server bringt keine dokumentierte native Funktion mit, um on-the-fly BLOB-Felder "lesbar" anzuzeigen, z.B wie in einem Hex-viewer bzw. in deinem Bildchen oben.

Gute Nachricht: so ein "BLOB-Reader" ist z.B. als Stored Procedure eben mal schnell zusammengeharkt.
Ein ganz nettes (ruhig angegangenes) Beispiel in 4 Akten findest du z.B im Database Journal als Storing Images and BLOB files in SQL Server Part 1-4 von Don Schlichting.

Aber ich würde überprüfen, ob nicht ein Anklicken einzelner Datensätze, also eine mehr oder weniger oberflächliche Stichproben-Ansicht ausreichen könnte.
Denn das dankt dir keiner, wenn du da eine Woche Arbeitszeit verbrätst.... und zu einem akzeptablen Preis verkaufen kannst du deine Erfahrungen auch eher nicht.

Grüße
Biber

Hallo und danke für die schnelle Antwort,

nee, Stichprobe ist leider nicht, denn das hab ich schon gemacht. Sind tatsächlich sehr sensible Daten, und es soll sichergestellt sein, dass ALLE Dokumente entfernt/verfremdet wurden. Daher dachte ich eben an eine solche Abfrage nach den Dateiheadern. Wenn ich eine solche Abfrage starten könnte (Zeige mir alle Datensätze die mit Dateiheader PDF, DOC, XLS, MSG, HTML anfangen) und dabei 0 Datensätze rauskäme, dann wäre das eine gute Überprüfung. Vor allem wenn ich diese Abfrage vorher auf der unveränderten Originaldatenbank absetzen würde und mir tatsächlich die Anzahl der Dokumente in unserer Datenbank angezeigt würde (hier würde ja ein Select Count ausreichen).

Kann ich überhaupt die Funktion SELECT in Verbindung mit BLOB verwenden?
Hat BLOB die Funktion SELECT?
Wenn ja, wie arbeitet MSSQL mit BLOBs, sprich in welchem Format?
"Liest" MSSQL die Felder tatsächlich binär? Oder Hex? Oder Reintext?

Bin leider kein Datenbankadmin...

Happy Robanukah,
Kaffeepause

Moin Kaffeepause,

ich weiß jetzt nicht, wie ich dir die Worte "der MSSQL-Server bringt keine dokumentierte native Funktion mit, um on-the-fly BLOB-Felder "lesbar" anzuzeigen" schonend und volksnah näherbringen kann...

Hast du mal den Link oben aufgerufen?

Die Hoffnung mit "Select Count() from whatever where irgendneSkalareFunktion(Blobfeld) operator 'bla%' " kannst du jedenfalls begraben AFIAK.

Grüße
Biber

Moin,

das mit dem select sehe ich anders als Biber. Mit einem

select * from Tabelle where Blob like '%PDF%'

kannst Du Dir eigentlich die Daten anzeigen lassen, die die Zeichenfolge 'PDF' enthalten. Das Blob (varbinary (max)) wird dann nämlich in varchar umgewandelt und durchsucht. Generell kann man sich also die zu suchende Hexfolge in varchar umwandeln, ebenso die varbinary-Spalte und dann mit like danach suchen.

Problem ist allerdings, daß in Hexfolgen sich dann auch Joker für die Suche mit like einschleichen können, wie z.B. das erste Zeichen im PDF-Header, 0x25 = %. Mit folgender kleiner Routine läßt sich aber auch das bewerkstelligen:

declare @SuchHex varbinary (500), @SuchText varchar (500)

-- hier die zu suchende Hexfolge eintragen
select @SuchHex = 0x25504446

-- Ersetzung fuer Platzhalter
select @SuchText = convert (varchar (500), replace (replace (replace (replace (replace (
		@SuchHex, '[', '[[]'), '%', '[%]'), '^', '[^]'), '_', '[_]'), '-', '[-]'))  

select @SuchText = @SuchText + '%'	-- Suche am Anfang vom Text  
--select @SuchText = '%' + @SuchText + '%'	-- Suche ueberall im Text  
--select @SuchText = '%' + @SuchText	-- Suche am Ende vom Text  

select <Spalte>, convert (varchar (max), <Spalte>) from <Tabelle> where convert (varchar (max), <Spalte>) like @SuchText

Einfach oben die zu suchende Hexfolge eintragen und am Ende <Tabelle> und <Spalte> ersetzen. Falls nicht am Anfang, sondern überall oder am Ende gesucht werden soll, die entsprechende Zeile aktiv schalten und fertig.

Gruß, Mad Max

Hallo,

erstmal danke an beide für die Antworten!

mit

select * from Tabelle where Blob like '%PDF%'

erhalte ich

Der Argumentdatentyp varchar ist für das 2-Argument der like-Funktion ungültig.

Die Syntax der Abfrage wird allerdings von MSSQL problemlos anerkannt.

mit

select * from Tabelle where Blob like 0x2550

erhalte ich keine Fehlermeldung, allerdings auch keinen einzigen Treffer, was mich verwundert, denn nach der Joker-Erklärung habe ich eher mit zu vielen Ergebnissen gerechnet.

Mit der Routine erhalte ich eine Fehlermeldung

Meldung 529, Ebene 16, Status 2, Zeile 14
Die explizite Konvertierung des image-Datentyps in varchar(max) ist nicht zulässig.

Ich hab das Gefühl, dass das alles schon kurz vorm Ziel ist, deswegen würde ich mich sehr freuen, wenn sich die letzten Fehlermeldungen auch noch irgendwie beheben ließen.

Muss auch derweil mal schauen, was Google so zu den Fehlermeldungen sagt.

Viele Grüße,
Kaffeepause

Moin Kaffepause,

Ihr verwendet einen veralteten Datentyp, image. Der funktioniert zwar noch, ist aber im Prinzip abgelöst durch den Datentyp varbinary (max). Deswegen funktioniert auch die implizite Konvertierung beim einfachen select nicht. Dann muß man halt den Datentyp image erstmal in varbinary (max) konvertieren und dann erst in varchar (max). In der Suchroutine lautet die letzte Zeile dann also:

select <Spalte>, convert (varchar (max), convert (varbinary (max), <Spalte>)) from <Tabelle> where convert (varchar (max), convert (varbinary (max), <Spalte>)) like @SuchText

Gruß, Mad Max

Geil.

Einfach nur geil.

Ich bin im "normalen" Umgang mit MS schon bewandert (MCSE), aber im Bezug auf Datenbanken leider sehr wenig. Allein hätte ich das nie hingekriegt (ich kann den Code mit Mühe lesen und verstehen).

Vielen Dank!
Vielen, vielen Dank!

Moin Mad Max,

auch von mir vielen Dank... hab wieder was dazugelernt.

Die Convert( VarBinary (max), blobfeld) -Möglichkeit kannte ich noch nicht.

Grüße
Biber

Aber gerne doch. Und es freut mich, daß ich sogar Dir noch was Neues erzählen konnte, Biber

Gruß, Mad Max

Mist, eine Frage hab ich doch noch...
Tut mir Leid, falls diese Frage irgendwie dämlich sein sollte.

Ich möchte nach allen Datensätzen suchen, die weder Dateiheader1, noch Deateiheader2 entsprechen.
Das "weder" ist an sich leicht, da ich einfach ein "not like @suchtext" verwende.
Bei der Verknüpfung der beiden Konditionen tu ich mir grad schwer.

Folgendes habe ich erfolglos ausprobiert

select konvertiertes feld from tabelle where konvertiertes feld not like suchbegriff1 and where konvertiertes feld not like suchbegriff2
selectkonvertiertes feldfromtabellewhere (konvertiertes feldnot likesuchbegriff1) and (wherekonvertiertes feldnot likesuchbegriff2)
selectkonvertiertes feldfromtabellewherekonvertiertes feldnot likesuchbegriff1and not likesuchbegriff2
selectkonvertiertes feldfromtabellewherekonvertiertes feldnot likesuchbegriff1norsuchbegriff2//

Kurzum, ich eiere hier grad ziemlich rum...

Anbei der letzte Versuch.

declare @SuchHex1 varbinary (500), @SuchText1 varchar (500), @SuchHex2 varbinary (500), @SuchText2 varchar (500)

select @SuchHex1 = 0x00000000 -- Dateiheader1
select @SuchText1 = convert (varchar (500), replace (replace (replace (replace (replace (
		@SuchHex1, '[', '[[]'), '%', '[%]'), '^', '[^]'), '_', '[_]'), '-', '[-]'))  
select @SuchText1 = @SuchText1 + '%'  

select @SuchHex2 = 0xAAAAAA -- Dateiheader2
select @SuchText2 = convert (varchar (500), replace (replace (replace (replace (replace (
		@SuchHex2, '[', '[[]'), '%', '[%]'), '^', '[^]'), '_', '[_]'), '-', '[-]'))  
select @SuchText2 = @SuchText2 + '%'  

select convert (varchar (max), convert (varbinary (max), TABELLE)) 
from tabelle.SPALTE 
where (convert (varchar (max), convert (varbinary (max), TABELLE)) not like @SuchText1) 
and
where (convert (varchar (max), convert (varbinary (max), TABELLE)) not like @SuchText2)

Viele Grüße,
die Kaffeepause

Moin Kaffeepause,

ich weiß nicht ganz genau, aus welchem kontext du diesen Code kopiert hast, aber...

?? Ist da nicht ein bisschen zu häufig das Schlüsselwort "select" drin?

Grüße
Biber

Witzig, Du hast anscheinend wirklich nur die richtige Möglichkeit ausgelassen: das "where" in der letzten Zeile muß weg, also "... and (convert (varchar ..."

@Biber
Den Code hat er überwiegend von mir da oben kopiert. Und ich habe mir für normale Zuweisungen an Variablen auch select statt set angewöhnt. Funktionieren aber genauso

Gruß, Mad Max

So, jetzt aber fertig.

Vielen Dank!

Das WHERE war das Problem.
Jetzt kann ich schön mehrere Suchbegriffe verknüpfen.

Das ist ganz toll geworden.
So, wie ich es hier brauche.