Problembeschreibung

Es kann schon mal durch unsaubere Scripte passieren, dass über GET-Requests in die Datenbank Formularinhalte in Felder eingetragen werden, bei denen die Sonderzeichen url-encodiert sind. So stünde dann dort statt des Leerzeichens %20.

Wenn man seine Tabelleninhalte nun von diesen Dingen befreiten möchte, z.B. per MySQL-Update-Anweisung, dann tritt ein Problem auf: Man will nach %20 suchen. Das geht aber nicht so einfach weil das %-Zeichen in MySQL als Wildcard verwendet wird. Man muss es also escapen. Die übliche Escape-Syntax wie z.B. in PHP durch "\" geht nicht.

 

Lösung

Das gute an der Lösung ist, man kann jedes beliebige Zeichen in MySQL als Escape-Zeichen definieren - mit dem Parameter ESCAPE.

Ein kompletter Query kann dann z.B. so aussehen:

 

SELECT * 
FROM `jos_vm_user_info`
WHERE `vm_sonstige` like '%!%%'
ESCAPE '!'
;

In diesem Fall wurde also das Ausrufezeichen als Escape-Zeichen definiert.

Bei Update-Befehl wird adäquat verfahren

update jos_vm_user_info
set `city` = replace(`city`,'%20','')
where `city` like '%!%20%'
ESCAPE '!'
;

 



 

Gleichzeitiges Suchen über mehrere Tabellenfelder

Dies als kleiner Nachtrag, weil oft im Zus.hg. mit obigen Problem anzutreffen. Hier hatten wir den Fall als Beispiel, dass URL-encoded Zeichen in die Tabelle geschrieben wurden und nun nachträglich entfernt werden sollen. Meistens passiert dieser Müll dann aber so, dass er in allen Feldern auftreten kann. Deshalb sollten wir auch nicht nur eine Spalte durchsuchen, sondern gleich mehrere oder alle. Dieses SQL-Suchkonstrukt wird aber dann schnell sehr umfangreich bei Tabellen mit vielen relevanten Spalten.

Hier soll eine Form gezeigt werden mit der dieser Vorgang syntaktisch abgekürzt werden kann. Die Idee dabei ist, vorher alle Inhalte der relevanten Felder als einen String zusammenzubauen und dann nur einmal in diesem String zu suchen. Des könnt dann z.B. so aussehen:

SELECT * 
FROM `jos_vm_user_info`
WHERE CONCAT_WS(' ', `last_name`,`first_name`,`company`,`middle_name`,`address_1`,`address_2`,`city`) like '%!%%'
Escape '!'
;

Falsch ist auf jeden Fall dieser Query, der so von MySQL nicht unterstütz wird:

Select *
FROM 'jos_vm_user_info'
WHERE * LIKE '%string%'
;

Achtung! das CONCAT sollte nur auf string-Felder angewendet werden. Wer bei seiner Suche auch nummerische Felder einbinden will, muss diese zunächst mit z.B. CAST(fax AS CHAR), umwandeln.

 

Weitere Beispiele:

Dieser Monsterquery sucht in einer Tabelle über alle Felder nach %-Zeichen:

SELECT * FROM 'jos_vm_user_info' WHERE ('user_info_id' LIKE '%!%%' ESCAPE '!' OR 'address_type' LIKE '%!%%' ESCAPE '!' OR 'address_type_name' LIKE '%!%%' ESCAPE '!' OR 'company' LIKE '%!%%' ESCAPE '!' OR 'title' LIKE '%!%%' ESCAPE '!' OR 'last_name' LIKE '%!%%' ESCAPE '!' OR 'first_name' LIKE '%!%%' ESCAPE '!' OR 'middle_name' LIKE '%!%%' ESCAPE '!' OR 'phone_1' LIKE '%!%%' ESCAPE '!' OR 'phone_2' LIKE '%!%%' ESCAPE '!' OR 'fax' LIKE '%!%%' ESCAPE '!' OR 'address_1' LIKE '%!%%' ESCAPE '!' OR 'address_2' LIKE '%!%%' ESCAPE '!' OR 'city' LIKE '%!%%' ESCAPE '!' OR 'state' LIKE '%!%%' ESCAPE '!' OR 'country' LIKE '%!%%' ESCAPE '!' OR 'zip' LIKE '%!%%' ESCAPE '!' OR 'user_email' LIKE '%!%%' ESCAPE '!' OR 'extra_field_1' LIKE '%!%%' ESCAPE '!' OR 'extra_field_2' LIKE '%!%%' ESCAPE '!' OR 'extra_field_3' LIKE '%!%%' ESCAPE '!' OR 'extra_field_4' LIKE '%!%%' ESCAPE '!' OR 'extra_field_5' LIKE '%!%%' ESCAPE '!' OR 'perms' LIKE '%!%%' ESCAPE '!' OR 'bank_account_nr' LIKE '%!%%' ESCAPE '!' OR 'bank_name' LIKE '%!%%' ESCAPE '!' OR 'bank_sort_code' LIKE '%!%%' ESCAPE '!' OR 'bank_iban' LIKE '%!%%' ESCAPE '!' OR 'bank_account_holder' LIKE '%!%%' ESCAPE '!' OR 'bank_account_type' LIKE '%!%%' ESCAPE '!' OR 'vm_sonstige' LIKE '%!%%' ESCAPE '!' OR 'vm_taetigkeitsfelder' LIKE '%!%%' ESCAPE '!' OR 'vm_mitarbeiter' LIKE '%!%%' ESCAPE '!' OR 'vm_homepage' LIKE '%!%%' ESCAPE '!' OR 'vm_bemerkungen' LIKE '%!%%' ESCAPE '!' OR 'vm_liefertermin' LIKE '%!%%' ESCAPE '!' OR 'vm_refnr' LIKE '%!%%' ESCAPE '!' OR 'vm_kdnr' LIKE '%!%%' ESCAPE '!' OR 'vm_bestellbemerkungen' LIKE '%!%%' ESCAPE '!' OR 'vm_firma' LIKE '%!%%' ESCAPE '!' OR 'vm_funktion' LIKE '%!%%' ESCAPE '!' OR 'vm_abteilung' LIKE '%!%%' ESCAPE '!' OR 'vm_wishusername' LIKE '%!%%' ESCAPE '!' OR 'vm_wishpswd' LIKE '%!%%' ESCAPE '!' OR 'vm_kundentyp' LIKE '%!%%' ESCAPE '!' OR 'vm_ustidnr' LIKE '%!%%' ESCAPE '!' OR 'vm_faxvorwahl' LIKE '%!%%' ESCAPE '!' OR 'vm_produktinfos' LIKE '%!%%' ESCAPE '!' OR 'vm_bonitaetkreditversicherung' LIKE '%!%%' ESCAPE '!' OR 'vm_coltogglestatus' LIKE '%!%%' ESCAPE '!' OR 'vm_bearbeiter' LIKE '%!%%' ESCAPE '!' OR 'vm_billmailadr' LIKE '%!%%' ESCAPE '!' OR 'vm_portoberechnen' LIKE '%!%%' ESCAPE '!');

 

Suchen und Ersetzen von %40 durch @, %2C durch Komma und %26 durch &:

update jos_vm_user_info set 'vm_name' = replace('vm_name','%2C',',') where 'vm_name' like '%!%2C%' ESCAPE '!';
update jos_vm_user_info set 'user_email' = replace('user_email','%40','') where 'user_email' like '%!%40%' ESCAPE '!';
update jos_vm_user_info set 'vm_firma' = replace('vm_firma','%26','') where 'vm_firma' like '%!%26%' ESCAPE '!';