CODEWITHSUNDEEP
phpsql
Jeremy Person
Jeremy Person

Reputation: 169

Escaping Quote for Variable in SQL Call

How do you add a single quote to a variable within a SQL statement? If I put 'jeremy' in place of the '\$user'\ variable it works perfectly. I can't figure out how to escape the quote for the variable in the SQL statement. Thank you for your help.

  $resultArticles = mysql_query("SELECT COUNT(id) FROM articleList WHERE user = '\$user'\ ");
  $totalArticlesLeaderboard = mysql_result($resultArticles, 0);
  echo "<strong>Total Articles: </strong>" . $totalArticlesLeaderboard;

Upvotes: 0

Views: 158

Answers (4)

PeeHaa
PeeHaa

Reputation: 72652

I've tried to find a suitable duplicate of your question, but I only found real dupes which are based on the ancient mysql_* functions. The mysql_* functions (like the ones you are using) are no longer maintained by the PHP commuity (for some time now) and the deprecation process has begun on it. See the red box?

You should really try to pick up the better PDO or MySQLi. Both of these option should be fine. Imho PDO has a better API, but mysqli is more towards mysql (in most cases PDO will do whatever you want to use it for).

With the two "new" API there is also the possibilty to use prepared statements. With prepared statements you should not have to worry about manually escaping values before inserting them into your queries.

An example of this using the PDO API would be:

$db = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $db->prepare('SELECT COUNT(id) FROM articleList WHERE user = :user');
$stmt->execute(array('user' => $user));

As you can see the values are not inserted directly into the query, but instead it uses placeholders. This code will make it impossible for people to inject arbitrary SQL into your query. And also you don't need to do any escaping anymore.

If you need more help in deciding between PDO or mysql check out the docs with more information about it. If you choose PDO you can find a good tutorial on the topic here.

Upvotes: 2

martin-lundberg
martin-lundberg

Reputation: 470

Test this

$resultArticles = sprintf("SELECT COUNT(id) FROM articleList WHERE user='%s", 
mysql_real_escape_string($user));

Upvotes: 1

joergy
joergy

Reputation: 21

If the variable $user contains any special characters, it is necessary to escape these, as shown in the first answer. If you don't have the mysql_real_escape_string() function available, use addslashes().

Upvotes: 0

Jordan Mack
Jordan Mack

Reputation: 8733

You should be able to just remove the escape characters:

$user = mysql_real_escape_string($user);
$resultArticles = mysql_query("SELECT COUNT(id) FROM articleList WHERE user = '$user'");

If you ever have trouble with variables, you can always just end the string and concatenate. I do this often to avoid confusion:

$user = mysql_real_escape_string($user);
$resultArticles = mysql_query("SELECT COUNT(id) FROM articleList WHERE user = '".$user."'");

As PeeHaa said, make sure you try to use PDO or MySQLi.

Don't forget to escape all user input, or they potentially can destroy your database. If you are using MySQLi, you can use mysqli::real_escape_string. Sanitizing ALL your user data is absolutely essential. DO NOT SKIP THIS!

Upvotes: 0

Related Questions