Solved

php sql security

Posted on 2011-09-26
7
265 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 82

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Query concatenation 12 46
CodeIgniter XSS confusion 5 51
complicated query 15 44
Install MySQL 5.6 and PHP on Centos Linux 6 54
Cybersecurity has become the buzzword of recent years and years to come. The inventions of cloud infrastructure and the Internet of Things has made us question our online safety. Let us explore how cloud- enabled cybersecurity can help us with our b…
Creating and Managing Databases with phpMyAdmin in cPanel.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now