Use a function which mimics the original mysql_real_escape_string

Question

I try to extract some information from one table and insert it to another. I'm using the following function from https://php.net/mysql_real_escape_string to handle the escape characters.

<?php 
function mysql_escape_mimic($inp) { 
    if(is_array($inp)) 
        return array_map(__METHOD__, $inp); 

    if(!empty($inp) && is_string($inp)) { 
        return str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"), array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $inp); 
    } 

    return $inp; 
} 
?>

The string I deal with is in html form with double and single quotes like

<input type="radio" value="choice_3" checked="true"/> Eat pig's belly and swine's matrix

I have to use string concatenation to write queries because column names and table names are dynamic.

$query .= "'".mysql_escape_mimic($string)."', ";

I know there is some kind of syntax error but I don't know how to fix it. Can anyone help me with it? Thanks.

You should not escape data in SQL. Use prepared statements.

Dharman
– Dharman ♦

2019-07-03 08:20:35 +00:00
Commented Jul 3, 2019 at 8:20 — Dharman
– Dharman ♦, Commented Jul 3, 2019 at 8:20

landru27 · Accepted Answer · 2019-07-03 04:08:09Z

1

I suspect your problem is with this line:

$query .= "'".mysql_escape_mimic($string)."', ";

That concatenation will leave a trailing comma, which almost certainly is causing a syntax error in your SQL. In SQL, any set of terms that are to be separated by commas must not have a trailing comma at the end of that set.

You can use a PHP trimming function to trim off the trailing ", " after you are done building the concatenated string.

I would also like to note that you can accomplish what your stated goal is ("extract some information from one table and insert it to another") entirely within the database. That is, you don't need to SELECT it into your application and then re-INSERT into the other table, thus avoiding this problem entirely.

If the two tables have identical columns, then something like this should work:

INSERT INTO table2 SELECT * FROM table1 WHERE condition;

If the two tables do not have identical columns, then something like this should work:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT columnA, columnB, columnC, ...
FROM table1
WHERE condition;

I cribbed these directly from w3schools.com. You can search for many such examples using the search string "mysql select from one table into another".

answered Jul 3, 2019 at 4:08

landru27

1,71214 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Cassie Liu Over a year ago

Sorry I didn't explain it clearly. $query .= "'".mysql_escape_mimic($string)."', "; is just part of the concatenation and the real problem is about the escaped character. A query like

INSERT INTO problem (content) VALUES ('<input type="radio" value="choice_3" checked="true"/> Eat pig's belly and swine's matrix')

obviously can't work. Also, since I need to modify the content, I can't use the query you suggest.

landru27 Over a year ago

first: you are correct, that won't work; but what is the value of the string you are trying to input after it has gone through your mysql_escape_mimic() function? isn't the purpose of that function to take care of the quoting (and other metacharacters)?

landru27 Over a year ago

second: regardless of the value of the string you are trying to input after it has gone through your mysql_escape_mimic() function, the ", " tacked on by the statement I referenced is almost certainly wrong: for a single value, a ", " is in error and for an iterated set of values the final ", " is in error

landru27 Over a year ago

I used your PHP to process your input HTML, and the escaped form of that HTML INSERTs just fine; I made a SQLFiddle showing this; is your PHP code above not producing the escaped form of the string that you expect? it is for me ...

Cassie Liu Over a year ago

I just realized that I made a stupid mistake regarding the use of the function. Thanks anyway.

Collectives™ on Stack Overflow

Use a function which mimics the original mysql_real_escape_string

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related