0

Possible Duplicate:
Best way to prevent SQL Injection in PHP

In the code below, the field title and the variable $submission is a name in text. The query usually works just fine. However, if title and $submission contain an apostrophe, the query does not include results.

How can I make the query work with apostrophes?

$submission = $_GET['submission'];

$query2 = "SELECT
               title, 
               1ad1, 
               1adcit, 
               1adst, 
               zip, 
               1adph, 
               1site, 
               neighborhood
           FROM 
               submission 
           WHERE 
               title = '$submission'
           ORDER BY 
               neighborhood ASC";
Community
  • 1
  • 1
John
  • 4,820
  • 21
  • 62
  • 92
  • 8
    **Your code is vulnerable to SQL injection.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL (this will also solve your problem). If you don't know what I'm talking about, or how to fix it, read the story of [Bobby Tables](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). – eggyal Jun 05 '12 at 15:29
  • @eggyal Thanks... how would I turn the simple query about into a prepared statement? – John Jun 05 '12 at 15:34
  • 1
    @John — The [example in the linked to answer](http://stackoverflow.com/a/60496/19068) is not complicated. – Quentin Jun 05 '12 at 15:35
  • @John: Follow the first link "prepared statements" in my comment. – eggyal Jun 05 '12 at 15:35

2 Answers2

0

encodeURI() of javascript, is your friend, in an absolute way. https://developer.mozilla.org/en/Referencia_de_JavaScript_1.5/Funciones_globales/encodeURI

however it won't solve your apostrophe problem since this is a perfectly allowed character. You have to filter it in php before sql manipulation, with either:

  • mysql_real_escape_string
  • mysqli_real_escape_string
  • pdo prepared statements.

regds.

Sebas
  • 21,192
  • 9
  • 55
  • 109
  • 1
    There is no indication that the form data is being constructed using JavaScript, and W3Schools is a terrible reference – Quentin Jun 05 '12 at 15:31
  • A Spanish reference isn't too useful on an English site either … and it is still irrelevant. – Quentin Jun 05 '12 at 15:34
  • damned :D automatic language detection from website, i'm not spanish.. my computer is – Sebas Jun 05 '12 at 15:34
0

You need to escape characters that are special to MySQL. That's where mysql_real_escape_string() is required. Use it on all data that may contain special characters such as apostrophes.

John Conde
  • 217,595
  • 99
  • 455
  • 496
  • Don't use mysql_real_escape_string, use [bound arguments](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php/60496#60496). – Quentin Jun 05 '12 at 15:32
  • 1
    Prepared statements are vastly preferable. Especially considering the PHP manual page now expressly says, **don't use [`mysql_real_escape_string`](http://docs.php.net/manual/en/function.mysql-real-escape-string.php)** –  Jun 05 '12 at 15:32
  • My answer was tailored towards their implementation. Naturally bound arguments are better but I'm not going to make my answer, "rewrite your application". – John Conde Jun 05 '12 at 15:32