0

I have a php page that dislplays info from a mysql table like this

$query = "SELECT * FROM `diccionary` WHERE `entry` LIKE 'a%' ORDER BY `entry`";

and each result I use it as input for a second query to a different php file:

$escaped = mysql_real_escape_string($row['entry']);
echo "<a href=editor.php?entry=$escaped>".$row['entry']."</a> |

some of te words that are returned at this page have next formats ant they are returned this way:
word1 word2
word'
word+
word (-)

but they are not sent correctly at the href=editor.php?entry=$escaped part, actually only the ones ended in apostrophe are ok because I'm using the "mysql_real_escape_string" function, I tried converting the others into escaped characters but it didn't work, like ("+", "+", $row['entry']). These are the links I see in all these cases:

2 words = editor.php?entry=word1 (without the following space nor word2, this gives me back all the words, if any, which match with word1, but not the compound of word1 word2).

word' = editor.php?entry=word\' (which is correct because of the function, and it also gives me back the correct word).

word+ = editor.php?entry=word+ (the + should be escaped because if I click on the produced link it gives me no results, blank page).

word (-) = editor.php?entry=word (this is similar to the case of 2 words, and besides it has the parenthesis which should be escaped also, this one also produces blank page).

I've been looking arround and I could only find the way of fixing the apostrophe, I don't know how to fix the rest of cases, Any help would be valuable.

Thanks a lot.

Andrés Chandía
  • 999
  • 1
  • 16
  • 32
  • why would you use mysql_real_escape_string on mysql results? – Prashank Feb 03 '14 at 17:04
  • you shouldn't be sending escaped data to the client in the urls. sql escaping is for use in sql statements, not http urls. sql escaping is the **LAST** thing you do to a particular string before inserting it into an sql statement. not EVER something you do first and then hope the escaping doesn't get trashed by all the intermediate systems. – Marc B Feb 03 '14 at 17:04
  • @Prashank because of the resulting link I'm generating. – Andrés Chandía Feb 03 '14 at 17:06
  • @Marc B, Sorry, I'm not an expert and I do it the way I find it works, please suggest me a better way for me to learn it and apply it. – Andrés Chandía Feb 03 '14 at 17:07
  • don't you need http://php.net/urlencode for that? – Prashank Feb 03 '14 at 17:07
  • then I suggest you read through http://bobby-tables.com for how to avoid sql injection, because right now your avoidance techniques are worse than useless. You THINK you're secure while having done nothing at all to avoid the actual problem. – Marc B Feb 03 '14 at 17:09
  • @Prashank so I should use something like: $escaped = urlencode($row['entry']); ? – Andrés Chandía Feb 03 '14 at 17:10
  • @AndrésChandía Yes. I hope you correct the variable name though. – Prashank Feb 03 '14 at 17:11
  • @MarcB Thanks, I'll read it, I will try to understand it and apply it, so kind of you. – Andrés Chandía Feb 03 '14 at 17:12
  • @Prashank you mean .... – Andrés Chandía Feb 03 '14 at 17:14
  • Look at the top Related question with thousands of votes, "How can I prevent SQL injection in PHP?"; http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Jonast92 Feb 03 '14 at 17:15
  • Since you're a beginning with PHP, I'll start by telling you that the `mysql_xxx()` functions in PHP are obsolete. You should stop using them entirely. You should instead look up the newer PDO library and use that for all your DB access in PHP. With regards to escaping, your code has it backward; SQL escaping should be done on the *input* to the database, not on what it returns. However, PDO makes the whole concept much easier to get right. You should also learn about escaping strings that you're writing to HTML, using `htmlentities()`, and other forms of escaping. – Spudley Feb 03 '14 at 17:34
  • Here's a good tutorial to get you started with PDO and away from the old `mysql_xx()` functions: http://www.sitepoint.com/avoid-the-original-mysql-extension-2/ – Spudley Feb 03 '14 at 17:35

2 Answers2

1

You need to URL-encode your data for output into HTML href property.

$query_string = urlencode($row['entry']);
echo "<a href=editor.php?entry=$query_string".$row['entry']."</a> |

The mysql_real_escape_string function is used for escaping data for use in a query to MySQL not for use on the query results.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • As you can see above @Avneesh gave me the same solution, please check what I've answered him, how can I solve that?, any suggestion? – Andrés Chandía Feb 03 '14 at 18:55
  • @AndrésChandía It seems that you are still trying to use `mysql_real_escape_string()` somehow in outputting to browser. You should be using URL encoding only. `mysql_real_escape_string()` is intended ONLY for use in input to MySQL queries. – Mike Brant Feb 03 '14 at 19:29
  • Yes @Mike Brant, I don't know how else to do it, I'm looking for a better way but I don't understand very well how should I do it – Andrés Chandía Feb 03 '14 at 21:25
  • Ok, after all your suggestions I have reached a solution, I'm not certain that this is a good solution, after all your comments, so I put it here and I wait for your comments to improve this, thanks again:Ok, after all your suggestions I have reached a solution, I'm not certain that this is a good solution, after all your comments, so I put it here and I wait for your comments to improve this, thanks again: $preescaped = `mysql_real_escape_string($row['entry']); $escaped = urlencode($preescaped); echo "".$row['entry']." | ";` – Andrés Chandía Feb 04 '14 at 11:43
  • @AndrésChandía I still don't understand why you think you need to use `mysql_real_escape_string()` at all. You should just use `urlencode()` and nothing more. Again, the MySQL string escape functions are for use when preparing a string for use in a database query, not when preparing a string for use in a URL. – Mike Brant Feb 04 '14 at 21:56
  • because `urlencode()` works with everything except for the apostrophe, to which I use `mysql_real_escape_string()` – Andrés Chandía Feb 04 '14 at 23:25
  • @AndrésChandía `urlencode()` will encode apostrophes just fine. Try this: `$string="some'string";echo urlencode($string);` Output would be `some%27string`. – Mike Brant Feb 05 '14 at 00:26
  • Ok, may be the thing is that it do really encode it but not escape it, and I need it escaped to do the next query – Andrés Chandía Feb 05 '14 at 01:02
  • Sorry, it is not actually an apostrophe is a single quote, like in here: it's – Andrés Chandía Feb 05 '14 at 01:08
  • @AndrésChandía Then escape at the point you are about to do the query (i.e. after reading the value from `$_GET`) not before you even pass the parameter. Better yet, use prepared statements and don;t worry about escaping at all. – Mike Brant Feb 05 '14 at 01:42
  • Yes, that did it, I guess I didn't have enough skill to do this when I asked the question, but now I can see it clearer. Thanks a lot. – Andrés Chandía Feb 05 '14 at 10:20
0

try urlencode($row['entry']); Method http://in1.php.net/urlencode may be it can work.
But advice sending escaped data in url is not a good practice.

Rahil Wazir
  • 10,007
  • 11
  • 42
  • 64
Avneesh
  • 149
  • 1
  • 1
  • 7
  • this half worked, I mean ti works in all the cases that wasn't working at the beginning, but it does not work with apostrophe, I tried: $preesc = urlencode($row['entry']); $esc = mysql_real_escape_string($row['entry']); but it didn't solve it, any suggestion? – Andrés Chandía Feb 03 '14 at 17:23
  • now in that case you can try rawurlencode() http://in3.php.net/rawurlencode refer this u'll get some ideas. – Avneesh Feb 03 '14 at 17:33