Solved

php sql security

Posted on 2011-09-26
7
269 Views
Last Modified: 2012-05-12
I have the below query which limits users from submitting a form more than once per day. How could I write this SQL/SELECT statement in a secure way in my php code (to protect from injection, etc)?  Below includes the SQL select statement that phpMyAdmin output as php:

//one-a-day validation
$email = $data["email"];
$result = $this->_db->QuickArray(
    "SELECT count(*) AS ct\n"
    "FROM myTable\n"
    "WHERE email =\'$email\'\n"
    "AND submitdate >= curDate() \n"
    "AND submitdate < DATE_ADD(curDate(), INTERVAL 1 DAY)";
);
$foundSomething = $result[0];
if($foundSomething) {
                  return false;

Do I have to include something with "mysql_real_escape_string" for security ?
What is the \n for ?

thanks..
0
Comment
Question by:web5dev7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 36597548
"mysql_real_escape_string" is for escaping text on an INSERT or UPDATE.  It is not useful on a SELECT because a SELECT does not change any data.  \n is for the linefeed character.  It should not be there in the SQL statement and the SQL statement should be a single continuous string, not an array.  Unless you have code that puts that back together, you can't use that as a query.  At least not in any way that I know about.
0
 
LVL 13

Expert Comment

by:dsmile
ID: 36597560
Yes, you should use mysql_real_escape_string to prevent security issue.

Just like this

$email = mysql_real_escape_string($data["email"]);

\n is for line break on linux/unix
0
 
LVL 13

Expert Comment

by:dsmile
ID: 36597566
Damn slow network.
I don't mean to double post, sorry
0
Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

 
LVL 11

Accepted Solution

by:
maeltar earned 250 total points
ID: 36598027
I use the following function :

function check_input($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
  {
  $value = stripslashes($value);
  }
// Quote if not a number
if (!is_numeric($value))
  {
  $value = "'" . mysql_real_escape_string($value) . "'";
  }
return $value;
}

Open in new window


which you would use in your example :

//one-a-day validation

$email = check_input($data["email"]);

$result = $this->_db->QuickArray(
    "SELECT count(*) AS ct "
    "FROM myTable "
    "WHERE email =\'$email\' "
    "AND submitdate >= curDate() "
    "AND submitdate < DATE_ADD(curDate(), INTERVAL 1 DAY)";);

$foundSomething = $result[0];
if($foundSomething) {
                  return false;
}

Open in new window

0
 
LVL 11

Expert Comment

by:maeltar
ID: 36598036
Line 8 is supposed to read :

Note, quoting is no longer required in your query as it is done by the function as necessary.
    "WHERE email = $email "

Open in new window

0
 

Author Comment

by:web5dev7
ID: 36601743
hmmm... not working (throwing generic error). When I put back my old code it works.

Here is your code as it is on my page:

//check one-a-day input
function check_input($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
  {
  $value = stripslashes($value);
  }
// Quote if not a number
if (!is_numeric($value))
  {
  $value = "'" . mysql_real_escape_string($value) . "'";
  }
return $value;
}


//one-a-day validation

$email = check_input($data["email"]);

$result = $this->_db->QuickArray(
    "SELECT count(*) AS ct "
    "FROM myTable"
    "WHERE email = $email"
    "AND submitdate >= curDate() "
    "AND submitdate < DATE_ADD(curDate(), INTERVAL 1 DAY)";
);

$foundSomething = $result[0];
      if($foundSomething) {
                        return false;
}
0
 

Author Closing Comment

by:web5dev7
ID: 36748768
pretty close - eventually got it to work.
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
One of the biggest threats facing all high-value targets are APT's.  These threats include sophisticated tactics that "often starts with mapping human organization and collecting intelligence on employees, who are nowadays a weaker link than network…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question