MySQL: Αναζήτηση σε json column (χωρίς JSON_CONTAINS) / PHP: Πρόβλημα κανονικής παράστασης

[philos]

Έχω δύο θεματάκια προς αντιμετώπιση

Όποιος μπορεί να με διαφωτίσει έστω για ένα, ευχαριστώ εκ των προτέρων!

 

Α) Έχουμε ένα column σε MySQL πίνακα (MariaDB βασικά), το οποίο έχει json δεδομένα. Παράδειγμα:

{"message":"Phone number information as of 2018-01-05 19:52:25 UTC","type":"cellphone","provider":"Vodafone","ported":false,"success":true}

Ξέρω, δεν είναι και ό,τι πιο κανονικοποιημένο, αλλά ο server τρέχει MariaDB < 10.2.3 συνεπώς δεν είναι διαθέσιμη η JSON_CONTAINS αυτή τη στιγμή.

Το ζητούμενο: θέλουμε να κάνουμε αναζήτηση βάσει provider σε αυτό το column. Είχα γράψει αυτό, με το query στο $searchprovider:

AND (phone_info LIKE '%\"provider\":\"". $db->escape_string($searchprovider) ."\"%')

Δουλεύει, όμως:

- αντιμετωπίζει προβλήματα σε περιπτώσεις που υπάρχουν > από μία λέξεις (πχ για query "Vodafone" θα δουλέψει σίγουρα)

- αν υπάρχουν παρενθέσεις στον provider, πχ: "provider":"RCS \u0026 RDS (Digi)", δεν βρίσκεται αποτέλεσμα.

Καμιά ιδέα;

 

Β) Σε μια μεταβλητή $bbcode, έχουμε κείμενο με κώδικα BBCODE, που προσεσσάρεται για να αντικατασταθούν οι εικόνες (  [ img ] tags) σε html (<img src= κτλ):

$bbcode = preg_replace('#\[img\]\s*(https?://([^*\r\n]+|[a-z0-9/\\._\- !]+))\[/img\]#iUe', "\$this->handle_bbcode_img_match('\\1')", $bbcode);

...με:

	function handle_bbcode_img_match($link, $fullsize = false)
	{
		// remove double spaces -- fixes issues with wordwrap
		$link = str_replace(array('  ', '"'), '', $link);

		$retval = ($fullsize ? '<div class="size_fullsize">' : '')  . '<img src="' .  $link . '" border="0" alt="" />' . ($fullsize ? '</div>' : '');

		return $retval;
	}

Το θέμα: πρέπει κάπως να αλλάξει ο κώδικας $bbcode = preg_replace ή και η callback function, ώστε:

α) οι καθαρές εικόνες

[img ]link[ /img]

...να προσεσσάρονται με έναν Α τρόπο (τιμή html στη $retval)

β) οι εικόνες που εσωκλείονται σε url:

[url = "www.google.gr" ][img ]link[ /url][ /url]

... να προσεσσάρονται με Β τρόπο (άλλη $retval)

Δυστυχώς δεν ξέρω κανονικές παραστάσεις ώστε να προσαρμόσω τη preg_replace και δυσκολεύομαι ακόμα και με συμφραζόμενα να βγάλω σωστό αποτέλεσμα...

(ΥΓ: Έβαλα κενά τα img και url tags γιατί δεν τα πρόβαλλε σωστά η φόρμα εδώ )

 

Ευχαριστώ και πάλι!

Επεξ/σία 16 Ιανουαρίου 2018 από philos

[TheDarkKnight]

Δοκίμασε το περιεχόμενο για το like να το περνάς δυναμικά πχ.

$provider = "something";

$info = '%,"provider":"' . $provider . '",%';

$stmt = mysqli_prepare($link, "SELECT something FROM table WHERE condition1 AND phone_info LIKE ?");

mysqli_stmt_bind_param($stmt, 's', $info);

 

Επεξ/σία 16 Ιανουαρίου 2018 από TheDarkKnight

[philos]

Γεια σου TheDark και thanks για την απάντηση!

 

Μήπως υπάρχει κάποια προτεινόμενη λύση με χρήση απλής WHERE condition (πχ με πιο σωστό LIKE ή REGEXP ) ;

Το query τρέχει σε μηχανή vBulletin όπου δεν χρησιμοποιούνται οι mysqli functions. Εγώ απλά τρέχω το query μέσω του $db->query() με ανάλογες WHERE.

 

Επειδή υπάρχουν κι άλλα conditions βάσει αναζήτησης, τα περνάω όλα στη $filter_where. Μια άλλη δοκιμή που έκανα είναι κι αυτή:

$filter_where .= " AND (phone_info REGEXP '\"provider\":\"[^\"]*[[:<:]]" . $db->escape_string($searchprovider) . "[[:>:]]') ";

... όμως, προφανώς δεν είναι ασφαλές:

- πχ αν αναζητήσω για καθαρές λέξεις (το κριτήριο πάει στη $searchprovider) πχ: Vodafone ή RCS & RDS

...βγάζει αποτέλεσμα

- αν αναζητήσω όμως σε κάτι με παρενθέσεις, πχ: RCS & RDS (Digi)  ... δεν το βρίσκει κι ας υπάρχει

- αν αναζητήσω κάτι με μία παρένθεση:  RCS & RDS (

...το βγάζει συντακτικό λάθος στο query (database error) και λογικό είναι.

Επεξ/σία 17 Ιανουαρίου 2018 από philos

[TheDarkKnight]

Κάνε escape μονό το κριτήριο αναζήτησης πχ

$provider = "RCS & RDS (";

$queryPart = '%{"provider":"' . db->escape_string(provider) . '%"},{"ported%';

$query = "SELECT something FROM sometable WHERE phone_info LIKE '" . $queryPart . "'";

Αν θες να ισχύει και το πχ "CS & RDS (":

$provider = "CS & RDS (";

$query = "SELECT something FROM sometable WHERE phone_info LIKE '%{\"provider\":\"%" . $db->escape_string(provider) . "%\"},{\"ported%'";

Αν μπορείς εκτύπωσε το query μετά από όλα αυτά για να έχουμε μια εικόνα για το τι φταίει.

 

Επεξ/σία 17 Ιανουαρίου 2018 από TheDarkKnight

[elvizakos]

Δεν ξέρω τη συμβατότητα με τις κανονικές εκφράσεις σε MariaDB και MySQL αλλά μπορείς να δοκιμάσεις να κάνεις escape τη $searchprovider με preg_quote :

$filter_where .= " AND (phone_info REGEXP '\"provider\":\"[^\"]*[[:<:]]" . $db->escape_string( preg_quote( $searchprovider ) ) . "[[:>:]]') ";

 

[philos]

Παιδιά τα δοκίμασα και τα δύο, αλλά δεν επιστρέφουν αποτέλεσμα

πχ elvizakos που δοκίμασα τελευταία τη λύση σου, το $filter_where παύει να επιστρέφει συντακτικό λάθος για: RCS & RDS (

(λόγω του ότι η παρένθεση γίνεται escape). Ορίστε το var_dump:

Αναφορά σε κείμενο

" AND (phone_info REGEXP '"provider":"[^"]*[[:<:]]RCS & RDS \\([[:>:]]') "

Όμως δεν εμφανίζει αποτελέσματα. Σας παραθέτω το παράδειγμα phone_info από τη βάση:

Αναφορά σε κείμενο

{"message":"Phone number information as of 2018-01-05 19:52:25 UTC","type":"cellphone","provider":"RCS & RDS (Digi)","ported":false,"success":true}

Αν ψάξω για: RCS & RDS

... το βρίσκει.

 

Και να πω ότι και αρχικά στη βάση, όταν πρωτοεισήχθει το value, το ampersand ήταν σε utf8 χαρακτήρα, δλδ:

Αναφορά σε κείμενο

{"message":"Phone number information as of 2018-01-05 19:52:25 UTC","type":"cellphone","provider":"RCS \u0026 RDS (Digi)","ported":false,"success":true}

(δεν ξέρω πως και γιατί δεν αποθηκεύτηκε ως &)

Καμιά λύση να περιλαμβάνει και τις δύο περιπτώσεις και να τις εντοπίζει το query;

Θα είχαμε ξεμπερδέψει πιο εύκολα αν είχαμε την JSON_CONTAINS, αλλά για τώρα πρέπει να βρεθεί λύση με LIKE ή REGEXP

[elvizakos]

2 ώρες πριν, philos είπε

το ampersand ήταν σε utf8 χαρακτήρα

δεν είναι utf-8. είναι unicode escaped χαρακτήρας και το φτιάχνει έτσι η συνάρτηση json_escape. (Δες JSON_UNESCAPED_UNICODE στη σελίδα http://php.net/manual/en/function.json-encode.php )

Βγάλε τα [[:<:]] και [[:>:]] από το query σου:

$filter_where .= " AND (phone_info REGEXP '\"provider\":\"[^\"]*" . $db->escape_string( preg_quote( $searchprovider ) ) . "') ";

Για το δεύτερο θέμα σου τώρα, δεν είμαι σίγουρος αν μπορεί να γίνει σωστά με ένα μόνο preg_replace... Μια λύση ίσως είναι αυτή:

$bbcode = preg_replace_callback("@\[img\]\s*((?:http[s]?)?[:][/]{2}(?:[^\n\r\t ]+?))\s*\[\/img\]@imu", function($m){
	return "<img src=\"".htmlspecialchars($m[1])."\" />";
},$bbcode);

$bbcode = preg_replace_callback("@\[url(?:\s*[=]\s*[\"]([^\"]+)[\"]\s*)?\]\s*(.*?)\[\/url\]@imuS", function($m){
	if(!empty($m[1]))return "<a href=\"".htmlspecialchars($m[1])."\">{$m[2]}</a>";
	return "<a href=\"".htmlspecialchars($m[2])."\">{$m[2]}</a>";
},$bbcode);

 

[philos]

Καλησπέρα!

 

Λοιπόν, το θέμα με το query διορθώθηκε, ευχαριστώ πολύ!

 

Όσο για το 2ο θέμα, δεν δουλεύει όπως θα ήθελα.

Βασικά δουλεύει σωστά αυτό:

$bbcode = preg_replace_callback("@\[img\]\s*((?:http[s]?)?[:][/]{2}(?:[^\n\r\t ]+?))\s*\[\/img\]@imu", function($m){
	return "<img src=\"".htmlspecialchars($m[1])."\" />";
},$bbcode);

... δηλαδή οι εικόνες σε [ img ] [ / img] προσεσσάρονται με έναν Α' τρόπο (<img src= κτλ).

 

Όμως για τις - επίσης - εικόνες, που όμως παρεμβάλλονται από [ url = "xxxx"] [ img ] [ / img ] [/ url ], θέλω το δικό τους <img src να έχει άλλα στοιχεία (βασικά σκοπεύω να βάλω άλλη κλάση και ίσως και κάποιο onclick="" ). Νομίζω το 2ο part του κώδικα που έβαλες αφορά το προσεσσάρισμα απλά των links ( ? ).

 

Ευχαριστώ προκαταβολικά!

[elvizakos]

Μάλιστα... Δεν είμαι σίγουρος ότι έχω καταλάβει τι ακριβώς θέλεις να κάνεις με τις εικόνες... Ναι όντως, το δεύτερο preg_replace απλά φτιάχνει τους συνδέσμους (αυτό κατάλαβα στην αρχή). Anyway, δεν ξέρω αν αυτό είναι αυτό που ζητάς και μπορεί να σε βοηθήσει, αν ναι, προσάρμοσέ το για τις ανάγκες σου:

<?php

$bbcode = preg_replace_callback("@(\[url(?:\s*[=]\s*[\"]([^\"]+)[\"]\s*)?\]\s*)?\[img\]\s*((?:http[s]?)?[:][/]{2}(?:[^\n\r\t ]+?))\s*\[\/img\](\s*\[/url\])?@imu", function($m){

# html για την εικόνα αν είναι μέσα σε [url] (άλλαξε το html όπως το θέλεις)
if(!empty($m[1]))return "<a href=\"".htmlspecialchars($m[2])."\"><img class=\"1\" src=\"".htmlspecialchars($m[3])."\" /></a>";

# html για την εικόνα αν δεν είναι μέσα σε [url] (άλλαξε το html όπως το θέλεις)
return "<img class=\"2\" src=\"".htmlspecialchars($m[3])."\" />";
},$bbcode);

?>

Ελπίζω να βοήθησα!