Solved

php sql security

Posted on 2011-09-26
7
268 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 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