how to escape a string in php?

Question

Possible Duplicate:
How to properly escape a string via PHP and mysql

I'm trying to populate a MySQL database, and to do so I'm parsing a data file and running a INSERT INTO ... query. the table parsonspredictions_R is structured as:

+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| drug_a      | blob    | NO   |     | NULL    |                |
| drug_b      | blob    | NO   |     | NULL    |                |
| correlation | float   | NO   |     | NULL    |                |
| p_value     | float   | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

However, there are some drug_x values which have ' along, so how could I escape my string in other to ignore or remove those ' from the string?

Example:

INSERT INTO parsonspredictions_R (
    drug_a,
    drug_b,
    correlation,
    p_value
) VALUES(
   '2'-Hydroxyflavanone_28_0',
   'Emodin',
   0.165714,
   0.0019
);

Results in:

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '', 'Emodin', 0.165714, 0.0019 )' at line 1-b

@Cups not what *database*, but what *library* to access that database. There are 3 in core PHP alone: the old mysql, the newer mysqli, and PDO. — Pekka, Aug 04 '12 at 21:58
See [string literals in MySQL](http://dev.mysql.com/doc/refman/5.0/en/string-literals.html) which talks about escaping. (The `'form'` should be preferred as `"form"` is in violation of ANSI.) However, if being called from, say, PHP (and not just a final query as shown), use proper techniques (ie. placecholders) to prevent [SQL injection attacks](http://en.wikipedia.org/wiki/SQL_injection). — , Aug 04 '12 at 21:59

Jose Vega · Answer 1 · 2012-08-04T22:11:07.947

1

Try using this function mysqli_real_escape_string if you are using mysqli, and if you are not using mysqli a simple google search for: escapes special characters in a string PHP would point you to a solution.

If you want to avoid this problem completely you should try using the PDO class and their take on prepared statements.

Assuming you are using mysqli

$drug_a = mysqli_real_escape_string($drug_a);
$drug_b = mysqli_real_escape_string($drug_b);


INSERT INTO parsonspredictions_R (
    drug_a,
    drug_b,
    correlation,
    p_value
) VALUES(
   $drug_a,
   $drug_b,
   0.165714,
   0.0019
);

edited Aug 04 '12 at 22:11

answered Aug 04 '12 at 21:57

Jose Vega

10,128
7
40
57

@philippe then *specify what database library you are using* – Pekka Aug 04 '12 at 21:59
3

@philippe - `mysqli` is a PHP *updated* version of the `mysql` native library. It *is* MySQL. – Jared Farrish Aug 04 '12 at 21:59

user15 · Accepted Answer · 2012-08-06T07:48:31.143

0

You can escape '-character by typing

INSERT INTO parsonspredictions_R (
    drug_a,
    drug_b,
    correlation,
    p_value
) VALUES(
    '2''-Hydroxyflavanone_28_0',
    'Emodin',
    0.165714,
    0.0019
);

Or you can put your string in MySQL query in ""-quotes.

edited: To make your file ready for regex you can use mysql_real_escape_string() on text before using regex. It will escape all suspicious characters, like '-character.

edited Aug 06 '12 at 07:48

answered Aug 04 '12 at 22:00

user15

1,044
10
20

+1 because this is correct and does not deserve a downvote. However, since the question is tagged as PHP it might be advisable to consider how "dynamic data" is inserted. – Aug 04 '12 at 22:06
@Ianka: the data comes from a file ... which is being parsed using a regex, so I can't escape it manually. – cybertextron Aug 04 '12 at 22:08
@philippe than you can edit your regex (or make another one) to add '' (2 quote symbols) instead of ' (one quote symbol) in text variables – user15 Aug 04 '12 at 22:11
Take this value: `Spiro[cyclohexane-1,2'-[2H]cyclopentapyrimidin]-4'(3'H)-one, 1',5',6',7'-tetrahydro- (9CI)_48_3`: I'm using the following regex: – cybertextron Aug 04 '12 at 22:17
`$regex = '/(\w*)\s*-\s*([\d\.]+)\s*$p\s*=\s*([\d\.]+)$/';` – cybertextron Aug 04 '12 at 22:18
you put result from your regex work in some array, yes? then just walk throug this array and change all single qoute symbol on 2 single quote symbols, there will be 2 nested loops and all your string will be escaped. You even don't need a regex for this, you can use simple `str_replace` php function. – user15 Aug 04 '12 at 22:25
@lanka Don't make me take away my +1! `str_replace` is **not suitable** for preparing SQL data. Search on SO for "PHP sql injection attack". – Aug 04 '12 at 22:48
@pst but these strings are come from file, there is no user input from posting forms, so there can not be any injection attack. But I'll edit the post and say that str_replace is suitable only for limited situations like this one and not suitable for most other situations. – user15 Aug 05 '12 at 09:39
@lanka **It does not matter**. Please don't spread the use of a one-off approach that will "sometimes work" (it won't work when the input contains a '\', for instance) when the *accepted approaches* (either using `mysql_real_escape_string` or, better, *placeholders*) will **always work** and do not have issues with invalid data: from a crafty attacker or just unexpected file input. (Changed from +1 to -1.) – Aug 06 '12 at 00:29
@rst ok im not spreading – user15 Aug 06 '12 at 07:49

how to escape a string in php?

2 Answers2