DaWolf has asked for the wisdom of the Perl Monks concerning the following question:

Greetings, fellow monks.

First of all it's good to be here again, since I'm involved in a PHP project, I was out of the Perl world for a long time.

I have a nasty problem with a regex question. Yes, I'm honestly doing it in PHP, but since regexes are almost standard, I was hoping someone here can help me.

I have a SQL statement that goes with something like this:

INSERT INTO TABLENAME VALUES ('bunch of text','another text');

My problem is that "a bunch of text" may contain simple quotes like I'm or 'cool'. Since I don't know how many simple quotes will appear or in what position of the text, I could only make a "wide open" and pretty stupid regex like:

/\'.*\'.*\'/

Is it even possible to achieve this level of searching in a regex?

Any thoughts would be greatefully appreciated.

Best regards,

my ($author_nickname, $author_email) = ("DaWolf","erabbott\@terra.com.br") if ($author_name eq "Er Galvão Abbott");

Replies are listed 'Best First'.
Re: Impossible RegEx?
by Aristotle (Chancellor) on Nov 20, 2002 at 18:44 UTC
    Is this a question about how to do it in Perl or PHP? In Perl, DBI has a quote() method, and placeholders do the quoting (if at all necessary) for you on their own. In PHP, look for an equivalent function. Update: See mysql_escape_string

    Makeshifts last the longest.

[reply]
Re: Impossible RegEx?
by jryan (Vicar) on Nov 20, 2002 at 22:25 UTC

    You are pretty ambiguous with your question. From what I can gleem, the problem is that you have some sort of query string, in which values got interpolated before being escaped. This is most easily fixable by properly escaping the value BEFORE it is inserted into the string. Of course, this wouldn't be a problem if you are using Perl and the DBI module; however, you are not, so use whatever solution PHP has available (there is some sort of add_slashes function, I can't quite remember the name; look it up on php.net).

    If you have no control over the values, then you have a much harder problem. Here is a way to fix it with Perl, although I'm not sure how much this will help you: 

    $_ = "INSERT INTO TABLENAME VALUES ('bunch o'f text','another text','a
+not'cool'her text')";

s/\G
(?<=')
    ((?:
          (?> [^\\']* )
        | \\.
                | '(?![,)])
    )*)
('[,)])
/fix("$1").$2/egx;


print;

sub fix {
    $_[0]=~s~(?<!\\)'~\\'~g;$_[0]
}

    [download]

    Of course, this won't even work for every case. If you have a string like: 

    INSERT INTO TABLENAME VALUES ('bunch o',f text','another text')

    [download]

    or 

    INSERT INTO TABLENAME VALUES ('bunch o')f text','another text')

    [download]

    Then this solution will break, and I honestly can't think of a way around it (at least off the top of my head). The best solution will be to try to fix the values BEFORE they are inserted into the query string.

[reply]
[d/l]
[select]
Re: Impossible RegEx?
by Abigail-II (Bishop) on Nov 20, 2002 at 18:43 UTC
    Eh, what is your question? What do you want to match? If all you want to know is whether there's a quote in the string. /'/ will do. If the problem is inserting into the table, regexes aren't needed at all. Use place holders.

    Abigail

[reply]
[d/l]
Re: Impossible RegEx?
by x31forest (Novice) on Nov 20, 2002 at 20:02 UTC
    I don't understand why you'd need a regex ?
    Do you want to replace ' with \' ?

    PHP has something called addslashes that will escape characters for you.

    -Paul
[reply]
Re: Impossible RegEx?
by Enlil (Parson) on Nov 20, 2002 at 18:44 UTC
    Not sure what you are asking here. Could you rephrase the question?, or what you are intending to do with the matches or just that they match? (I hope that I am myself clear in what I am asking).

    -enlil

[reply]
Re: Impossible RegEx?
by DaWolf (Curate) on Nov 21, 2002 at 18:22 UTC
    Sorry, folks, let me explain this better:

    This is a language independent question. I have to replace the extra simple quotes of the string with backslashed simple quotes so the sql statement becomes valid.

    The thing is this is a large text file (dozens of INSERT lines and just some of them have simple quotes), so I was wondering if there is a way to solve this little puzzle with a regex.

    Since regexes are very similar between Perl and PHP I was looking for something that you can call a "generic regex".

    The code part to execute the regex is easy to do in both languages.

    Thanks for your help,

    my ($author_nickname, $author_email) = ("DaWolf","erabbott\@terra.com.br") if ($author_name eq "Er Galvão Abbott");
[reply]

Back to Seekers of Perl Wisdom