String replace space

Question

I have two string like this:

$a = "John, Male , Central Java";
$b = "name = John and gender= Male";

I want these string to become:

$a = "'John','Male','Central Java'";
$b = "username='John' and gender='Male'";

What pattern and replacement would work with preg_replace to accomplish this?

I want to create a function like this:

function select($what, $from, $filter) {
    $query = "SELECT $what FROM $from WHERE $filter";
    // Performing mysql query.
}

$result = select("*", "user", "username = John and gender = Male");

$quer Output: SELECT * FROM user WHERE username = John and gender = Male

But the output is not valid mysql syntax. So I want the output to become:

SELECT * FROM user WHERE username='John' and gender='Male'

I also want to remove the space between symbols.

Solution:

I have try some pattern and replacement and finaly I found the solution. I've create function to format the query string. I also change the string setting with using && as AND and || as OR. So, even when the value string contains 'and' or 'or', it's would not affected by preg_replace.

// Preg replace sample: -> string = string space && string = space
function format_query($qry_string) {
    // Remove spaces before and after '='.
    $qry_string = preg_replace("/(\s+\=)/", "=", $qry_string); 
    // string= string space && string= space
    $qry_string = preg_replace("/(\=\s+)/", "=", $qry_string);
    // string=string space && string=space

    // Add single quote between '=' and word than contain space after the word.
    $qry_string = preg_replace("/\=(\w+\s+)/", "='$1", $qry_string); 
    // string='string space && string=space

    // Add single quote in first and end of words that start after '=' and not contain space after the word.
    $qry_string = preg_replace("/\=(\w+)/", "='$1'", $qry_string); 
    // string='string space && string='space'

    // Add single quote in end of words before of keyword '&&' and '||'.
    $qry_string = preg_replace("/(\w+)\s+\&\&/", "$1' &&", $qry_string); 
    // string='string space' && string='space'
    $qry_string = preg_replace("/(\w+)\s+\|\|/", "$1' ||", $qry_string);

    // Replate keyword '&&' and '||' to 'and' and 'or'
    $qry_string = str_replace("&&", "AND", $qry_string);
    $qry_string = str_replace("||", "OR", $qry_string);

    return $qry_string;
}

$string = "realname = Nanang El Agung || username = sarjono && password = 123456";
echo format_query($string);

OUTPUT: realname='Nanang El Agung' OR username='sarjono' AND password='123456'

This function is works fine for $b above.

You can use regex: split string into arrays, then `implode()`. — M. Suleiman, Mar 31 '13 at 18:00
If you use PDO, you could just use http://php.net/manual/en/pdo.quote.php — Ron van der Heijden, Mar 31 '13 at 18:00

samayo · Answer 1 · 2013-03-31T19:04:55.337

2

This just does that, for the first example, test it.

<?php 

$a = "John, Male , Central Java";


foreach ((explode(",", $a)) as $one) {

echo '\''.$one.'\',';
}

Outputs

'John',' Male ',' Central Java',

In relation to the Mysql query, something like this is feasible.

<?php 

    $a = "John, Male , Central Java";


    foreach ((explode(",", $a)) as $one) {

.

 function select($one) {  // will run the three statements, relatively..

.

   echo '\''.$one.'\',';
    }

Second one

$b = "name='John' and gender='Male'";

$b = str_replace('name', 'username', $b);

foreach((explode("=",$b[1]))as $one){
echo "$b";
}

edited Mar 31 '13 at 19:04

answered Mar 31 '13 at 18:03

samayo

16,163
12
91
106

Yes, the first sample is working. But how about the $b? It's containg " and " that need to splited as well. I think i can't explode it using "and" as pattern, since sometime this is a part of word, not keyword. e.g. `select("*", "table", "field like me and you and name = John")`, then the output will be `SELECT * FROM table WHERE field like 'me and you' and name='John'` – Nanang El Sutra Mar 31 '13 at 18:11
Ok, give me some minutes, I'll see what I can do – samayo Mar 31 '13 at 18:22
@NanangElSutra sorry, for being late, I can not seem to find a way to put a `'` around the names, but you can see my updated title --second one – samayo Mar 31 '13 at 19:06

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

Using preg_replace():

$a = "John, Male , Central Java";
$b = "name = John and gender= Male";

$a = preg_replace('/(\w+)((\s?\w+)+)/', '\'$1$2\'', $a);
// 'John', 'Male' , 'Central Java'

$b = preg_replace('/name\s?=\s?([\w\s]+) and gender\s?=\s?([\w\s]+)/', 'username = \'$1\' AND gender = \'$2\'', $b);
// username = 'John' AND gender = 'Male'

\w => [a-zA-Z0-9_]

\s => space

+ => 1 or more characters

? => 0 or 1 character

score 1 · Answer 3 · answered Mar 31 '13 at 18:07

1

The second part is

function select($what, $from, $filter) {
$query = mysql_fetch_array(mysql_query("SELECT {$what} FROM {$from} WHERE {$filter}")); extract($query);
return $what;
}

answered Mar 31 '13 at 18:07

Nik Drosakis

2,258
21
30

score 0 · Answer 4 · edited May 23 '17 at 12:29

0

Don't do it. You're trying to construct an SQL query with regular expressions. Your code (including all three current answers) will break on the first O'Connor that walks in the door. If you were to fix this and somehow get your code on the web, you'll be at the mercy of the first hacker who'll try a series of well-known exploits until they find a corner case you didn't think about.

See this question, and this answer, for the full details and the proper way to build your SQL statements.

edited May 23 '17 at 12:29

Community

1
1

answered Apr 03 '13 at 14:20

alexis

48,685
16
101
161

Hi @alexis, thanks for notice. I have know about that, but currenlty i'm building php framework to forward the query to multiple db engine. I also build some function to makes some query more easy. e.g. `_data::batch_insert('users|bio', 'username = john | passwd = 123456 | gender = male');` Although users type `DELETE TABLE` in their input, it's would automatically quoted by functions since every function only escape words from quotes if they are operator like `&&` as `"and"` or `<>` as `"LIKE"`. Users can also validate their string with `validate_string()`. Thanks. – Nanang El Sutra Apr 03 '13 at 21:34
Run your functions on the string `"realname = John; DROP TABLE user"`. Then re-read my answer, and the linked-to question. – alexis Apr 04 '13 at 11:12
Oh, function in my question is only sample. In my final function: `$input = "realname = John; DROP TABLE users; DROP TABLE address; exit;`. Then when I call my function the output is: `realname='John; DROP TABLE users; DROP TABLE address; exit;'`. I have try many SQL command and all output is quoted. Thx. – Nanang El Sutra Apr 04 '13 at 21:09
Sample? Then try "realname = John'; DROP TABLE users;", or whatever. Judging from your "sample", your code is definitely full of problems. Enjoy the ride. – alexis Apr 04 '13 at 22:55

String replace space

4 Answers4