PHP Apostrophe and query string

Question

I want to supply a SQL statement from the query string, but all my efforts result in escaped single quotes and slashes.

Answer 1

First, make sure you really really want to do that. This is ripe for an SQL Injection attack.

If you want something to run statements against the MySQL database, just use phpMyAdmin or MySQL workbench.

Answer 2

Assuming you are using mysql, use mysql_real_escape_string()

http://www.php.net/manual/en/function.mysql-real-escape-string.php

Answer 3

how can I implement this in a generic way?

All queries must be hardcoded in your script.
Of course some of them can be dynamically built, but you're allowed to make only DATA dynamic, not control structures.
So, it must be like this:

$name=mysql_real_escape_string($_POST['name']);
if ($id = intval($_POST['id'])) { 
  $query="UPDATE table SET name='$name' WHERE id=$id"; 
} else { 
  $query="INSERT INTO table SET name='$name'"; 
} 

or this:

if (!isset($_GET['id'])) {
  $query="SELECT * FROM table";  
} else {
  $id = intval($_GET['id'];
  $query="SELECT * FROM table WHERE id=$id";  
}  

or whatever.
Of course, inserted data must be properly escaped, cast or binded.

But sometimes we need to use dynamic operator or identifier. The principle is the same: everything must be hardcoded in your script, nothing to be passed from the client side to the SQL query directly.
Say, to make a dynamic sorting, you can use a code like this

$orders=array("name","price","qty");
$key=array_search($_GET['sort'],$orders));
$orderby=$orders[$key];
$query="SELECT * FROM `table` ORDER BY $orderby";

or to assemble a dynamic WHERE:

$w=array();
if (!empty($_GET['rooms'])) $w[]="rooms='".mysql_real_escape_string($_GET['rooms'])."'";
if (!empty($_GET['space'])) $w[]="space='".mysql_real_escape_string($_GET['space'])."'";
if (!empty($_GET['max_price'])) $w[]="price < '".mysql_real_escape_string($_GET['max_price'])."'";


if (count($w)) $where="WHERE ".implode(' AND ',$w); else $where='';
$query="select * from table $where";