1

I received the following error using my module:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 
You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near '%m-%d') = 
DATE_FORMAT(NOW(), '%Y-%m-%d') AND is_logout = 0' at line 1: SELECT id FROM 
ap_visits WHERE`current_ip` = '192.168.1.1' AND `url` = 
'http://www.mysite.com/php/4220/how-build-shopping-cart-w-checkout-php.html'' 
AND DATE_FORMAT(date_start, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d') AND 
is_logout = 0; Array ( ) in test_module_check() 
(line 164 of /var/www/clients/client1/web2/web/sites/all/modules/test_module/test_module.module).

The error here is in the value of url column. It has an apostrophe at the end. So my question is on how can I escape this value so the apostrophe will not be an issue?

I am using db_query. This is my code:

$sql = "SELECT id FROM ap_visits WHERE`current_ip` = '$current_ip' AND `url` = '$url' AND DATE_FORMAT(date_start, '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')  AND is_logout = 0";

$result = db_query($sql);
2
  • Hi. Did you tried with addslashes($url) ? Aug 14, 2013 at 4:46
  • @Jaypabs While Stone Vo's answer is perfectely correct, you can't rely on it alone. You need to read up on basic SQL Injection. Since you ask the question you ask, your site very likely has some serious security issues.
    – Letharion
    Aug 14, 2013 at 11:27

2 Answers 2

5

You should do

$sql = "SELECT id FROM {ap_visits}  WHERE current_ip = ':current_ip' AND url = ':url' AND DATE_FORMAT(date_start, '%%Y-%%m-%%d') = DATE_FORMAT(NOW(), '%%Y-%%m-%%d')  AND is_logout = 0";

$result = db_query($sql, array(':current_ip'=>$current_ip, ':url'=>$url));
0

Better to use the Database API, everything will be escaped properly

$query = db_select("ap_visits", "a")
    ->fields("a.id")
    ->condition("a.current_ip", $ip)
    ->condition("a.url", $url)
    ->where("DATE_FORMAT(a.date_start, '%%Y-%%m-%%d') = DATE_FORMAT(NOW(), '%%Y-%%m-%%d')")
    ->condition("a.is_logout", 0);

Output the query (for testing)

drupal_set_message($query->__toString());
drupal_set_message(print_r($query->arguments(),true));

Execute the query

$result = $query->execute();

Loop through the results

if ($result->rowCount())
{
    foreach ($result as $record)
    {
        //do stuff
    }
}
else
{
    // no data message
}

More here: https://drupal.org/developing/api/database

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.