2

I am storing user's liked pages from facebook in my postgres database, one of them being Sinead O'Connor's page. It seems like when it gets to the apostrophe, it terminates the query beacuse the apostrophes match and therefore results in an error:

  $json_likes=json_encode($likes);
  echo $json_likes;
  $query = "UPDATE public.account_recover_users SET user_likes='$json_likes' WHERE user_mail='$email'";
  $result = pg_query($query);
  if(!$result)
      exit('{ "status": false }');
  else exit('{ "status": true }');

Now what I get here is :

Warning: pg_query() [<a href='function.pg-query'>function.pg-query</a>]: Query failed: ERROR: syntax error at or near &quot;Connor&quot; LINE 1: ...l&quot;,&quot;page_id&quot;:&quot;243440865683470&quot;},{&quot;name&quot;:&quot;Sinead O'Connor&quot;,&quot;p...

Any idea how should I handle this? My app's users might have all sorts of charachters in their liked pages.

Mihai Bujanca
  • 4,089
  • 10
  • 43
  • 84
  • 2
    Any idea how should I handle this? Use parameterized queries or continue to suffer situations like this. [Here's how](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) PDO or mysqli over mySQL (Deprecated) – xQbert May 22 '13 at 12:32
  • Parameterize queries or sanitize input. **DO NOT** keep it as it is. – Geeky Guy May 22 '13 at 12:33
  • I am completely new to databases and webdev in general, sorry. and thank you – Mihai Bujanca May 22 '13 at 12:36
  • Seconded. @LoztInSpace is right. Working with databases without a minimum knowledge of SQL Injection is like working in an anti-bomb squad without any explosives training. – Geeky Guy May 22 '13 at 12:42
  • 1
    @LoztInSpace I have no boss (yet), I only am working in a team, for learning purposes- for now. But we also are trying to make our learning process result into a product or something. Thanks – Mihai Bujanca May 22 '13 at 12:44
  • Ok, well you need get some basics pretty fast. 1) Escape on the way out (that could be HTML, XML, SQL, CSV, whatever) 2) Use whatever parameterisation systems are available no matter what your input. ADVICE FOR PROPER DEVELOPMENT: If you find yourself adding strings together, you're doing it wrong. – LoztInSpace May 22 '13 at 12:51
  • @BujancaMihai In that case, you're doing things right :) exposing yourself to all kinds of situations and learning about stuff by breaking it are things that make good devs. – Geeky Guy May 22 '13 at 12:52

2 Answers2

5

Just be glad you're not trying to store the page for Bobby Tables. Your app is vulnerable to SQL Injection - do escape apostrophes, for your own good.

TL;DR: the apostrophes in the text you're appending are being matched to the ones hard coded in your application, malformatting the SQL command. Hence the error.

Edit: moving this here from the comments. Here is a good guide on how to prevent SQL Injection in PHP

Community
  • 1
  • 1
Geeky Guy
  • 9,229
  • 4
  • 42
  • 62
0

You have single quote in json string and it breaks the query. Use at least addslashes($json_likes) and addslashes($email)

Maxim Khan-Magomedov
  • 1,326
  • 12
  • 15
  • No, use `pg_espace_string`. `addslashes` is not adequate and won't work at all with modern PostgreSQL where backslash is a normal character. It's even mentioned in [its doc](http://www.php.net/manual/en/function.addslashes.php), quote: _It's highly recommended to use DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_string() for PostgreSQL)_ – Daniel Vérité May 22 '13 at 12:42
  • 1
    No that just wiggles around the problem. Use parameters properly. – LoztInSpace May 22 '13 at 12:44