#1
  1. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2002
    Location
    Queensland, Australia
    Posts
    827
    Rep Power
    142

    Match string not surround by quotes


    I need to do a string replacement within an SQL statement but only where the the pattern does not occur within a text string. This is to compensate for PHP PDOs lack of native functions for construction a CSV of placeholder in an IN statement.

    So I might have a query like:
    SELECT * FROM table where colId IN(XX).

    I would then search for "XX" and replace it with a generated string to produce
    SELECT * FROM table WHERE colId IN(?, ?, ?)

    That part is easy.

    What I want to avoid is:
    SELECT * from table WHERE colDesc "Hello XX World"
    becoming
    SELECT * FROM table WHERE colDesc "Hello ?, ?, ? World"
    Ooh, they have the Internet on computers now!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    832
    Rep Power
    496
    Well, the better you know about your data, the better you are off with regexes in general. We would need to know more on how hairy your SQL select statements can be: for example, can there be several quoted strings or only one? If yes, you could do the substitution only if the line does not match /".*XX.*"/.

    Another possibility: if the "XX" that you want to replace is always part of the string "IN (XX)", you could have something like:

    Code:
    /IN\s*\(XX\)/
  4. #3
  5. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    If you're using bound parameters you should never end up with arbitrary strings in your SQL query, so you shouldn't run into an issue as you described.

    There is really no way to use a reg ex to figure out whether "xx" is a placeholder in the sql query or part of a string.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2002
    Location
    Queensland, Australia
    Posts
    827
    Rep Power
    142
    Originally Posted by E-Oreo
    If you're using bound parameters you should never end up with arbitrary strings in your SQL query, so you shouldn't run into an issue as you described.
    You're right, as a rule of thumb there shouldn't be an existing string in the query.

    I thought if I could catch it, I would. But if I can't, well the query will fall over when executed anyway, at which point the programmer should remove the string or escape the placeholder.

    Thanks for the responses.
    Ooh, they have the Internet on computers now!

IMN logo majestic logo threadwatch logo seochat tools logo