Solved

php sql security

Posted on 2011-09-26
7
267 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
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 39
MS SQL Update query with connected table data 3 41
MS SQL Server time between records 14 47
SQL Server syntax 11 10
Creating and Managing Databases with phpMyAdmin in cPanel.
Provide an easy one stop to quickly get the relevant information on common asked question on Ransomware in Expert Exchange.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 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