473,323 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

sql injection prevention

hi im really new to sql and php but i was told that i needed to prevnt mysql injection and i dont really want to get it wrong i have done some reserch and aparently mysql_real_escape_string() should prevent it but im not quite sure were to put it if anyone could help me pls do thank you

here are my scripts

login
Expand|Select|Wrap|Line Numbers
  1. <?php 
  2. include 'dbc.php';
  3.  
  4. $full_name = mysql_real_escape_string($_POST['email']);
  5.  
  6. if ($_POST['Submit']=='Login')
  7. {
  8. $md5pass = md5($_POST['pwd']);
  9.  
  10. $sql = "SELECT id,full_name FROM users WHERE 
  11.             full_name = '$full_name.easehosting.co.uk' AND 
  12.             user_pwd = '$md5pass' AND user_activated='1'"; 
  13.  
  14. $result = mysql_query($sql) or die (mysql_error()); 
  15. $num = mysql_num_rows($result);
  16.     if ( $num != 0 ) { 
  17.        session_start(); 
  18.        list($user_id,$full_name) = mysql_fetch_row($result);
  19.         $_SESSION['user']= $full_name;  
  20.         if (isset($_GET['ret']) && !empty($_GET['ret']))
  21.         {
  22.         header("Location: $_GET[ret]");
  23.         } else
  24.         {
  25.         header("Location: /~$full_name/index.php");
  26.         }
  27.         //echo "Logged in...";
  28.         exit();
  29.     } 
  30.  
  31. header("Location: invalid.html");
  32. //echo "Error:";
  33. exit();        
  34. }
  35.  
  36. ?> 
register
Expand|Select|Wrap|Line Numbers
  1. <?php 
  2. session_start();
  3.  
  4.  
  5. include ('dbc.php'); 
  6.  
  7.  
  8. if ($_POST['Submit'] == 'Register')
  9. {
  10.    if (strlen($_POST['email']) < 5)
  11.    {
  12.     die ("Incorrect email. Please enter valid email address..");
  13.     }
  14.    if (strcmp($_POST['pass1'],$_POST['pass2']) || empty($_POST['pass1']) )
  15.     { 
  16.     //die ("Password does not match");
  17.     die("ERROR: Password does not match or empty..");
  18.  
  19.     }
  20.     if (strcmp(md5($_POST['user_code']),$_SESSION['ckey']))
  21.     { 
  22.              die("Invalid code entered. Please enter the correct code as shown in the Image");
  23.           } 
  24.     $rs_duplicates = mysql_query("select id from users where full_name='$_POST[full_name].easehosting.co.uk'");
  25.     $duplicates = mysql_num_rows($rs_duplicates);
  26.  
  27.     if ($duplicates > 0)
  28.     {    
  29.     //die ("ERROR: Account already exists.");
  30.     header("Location: register.php?msg=ERROR: Account already exists..");
  31.     exit();
  32.     }
  33.  
  34.  
  35.  
  36.  
  37.     $md5pass = md5($_POST['pass2']);
  38.     $activ_code = rand(1000,9999);
  39.     $server = $_SERVER['HTTP_HOST'];
  40.     $host = ereg_replace('www.','',$server);
  41.     mysql_query("INSERT INTO users
  42.                   (`user_email`,`user_pwd`,`country`,`joined`,`activation_code`,`full_name`)
  43.                   VALUES
  44.                   ('$_POST[email]','$md5pass','$_POST[country]',now(),'$activ_code','$_POST[full_name].easehosting.co.uk')") or die(mysql_error());
  45.     mkdir ("/var/www/hosts/$_POST[full_name].easehosting.co.uk", 0700);
  46.     mkdir ("/var/www/hosts/$_POST[full_name].easehosting.co.uk/docs", 0700);
  47.     mkdir ("/var/www/hosts/$_POST[full_name].easehosting.co.uk/CGI-BIN");
  48.     copy("/var/www/hosts/template/dbc.php","/var/www/hosts/$_POST[full_name].easehosting.co.uk/dbc.php");
  49.     copy("/var/www/hosts/template/index.php","/var/www/hosts/$_POST[full_name].easehosting.co.uk/index.php");
  50.     copy("/var/www/hosts/template/manage.php","/var/www/hosts/$_POST[full_name].easehosting.co.uk/manage.php");
  51.     copy("/var/www/hosts/template/docs/index.html","/var/www/hosts/$_POST[full_name].easehosting.co.uk/docs/index.html");
  52.     $file = fopen("/var/named/chroot/etc/named.conf", "a");
  53.     fwrite($file,"
  54.     zone '$_POST[full_name].easehosting.co.uk' {\n type master;\n file '/var/named/$_POST[full_name].easehosting.co.uk.hosts';\n };");
  55.     fclose($file); 
  56.  
  57.   $message = 
  58. "Thank you $_POST[full_name] for registering an account with $server. Here are your login details...\n\n
  59. ########################################################################### \n
  60. Your login name is: $_POST[full_name] \n
  61. Your domain name is: http://$_POST[full_name].easehosting.co.uk\n
  62. This email is to confirm that you have created an account with $server\n
  63. you can now login at http://www.easehosting.co.uk/login.html
  64. ########################################################################### \n
  65.  
  66. Thank you. This is an automated response. PLEASE DO NOT REPLY.
  67. ";
  68.  
  69.     mail($_POST['email'] , "Login details", $message,
  70.     "From: \"Auto-Response\" <notifications@$host>\r\n" .
  71.      "X-Mailer: PHP/" . phpversion());
  72.     unset($_SESSION['ckey']);
  73.     echo("Thank you $_POST[full_name], Registration was Successful! Your domain name is $_POST[full_name].easehosting.co.uk\n you can now login at www.easehosting.co.uk...");    
  74.  
  75.     exit;
  76.     }    
  77.  
  78. ?>
forgot
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. include 'dbc.php';
  3. if ($_POST['Submit']=='Send')
  4. {
  5. $host = $_SERVER['HTTP_HOST'];
  6. $rs_search = mysql_query("select user_email from users where user_email='$_POST[email]'");
  7. $user_count = mysql_num_rows($rs_search);
  8.  
  9. if ($user_count != 0)
  10. {
  11. $newpwd = rand(1000,9999);
  12. $host = $_SERVER['HTTP_HOST'];
  13. $newmd5pwd = md5($newpwd);
  14. mysql_query("UPDATE users set user_pwd='$newmd5pwd' where user_email='$_POST[email]'");
  15. $message = 
  16. "You have requested new login details from $host. Here are the login details...\n\n
  17. User login: $_POST[full_name] \n
  18. Password: $newpwd \n
  19. ____________________________________________
  20. *** LOGIN ***** \n
  21. To Login: http://$host/login.php \n\n
  22. _____________________________________________
  23. Thank you. This is an automated response. PLEASE DO NOT REPLY.
  24. ";
  25.  
  26.     mail($_POST['email'], "New Login Details", $message,
  27.     "From: \"Auto-Response\" <robot@$host>\r\n" .
  28.      "X-Mailer: PHP/" . phpversion());
  29.  
  30. die("Thank you. New Login details has been sent to your email address");
  31. } else die("Account with given email does not exist");
  32.  
  33. }
  34. ?>
thank you
oliver
Feb 5 '09 #1
3 1975
Atli
5,058 Expert 4TB
Hi.

Generally, to avoid SQL Injection, you need to validate any piece of data that is going to go into a dynamically created SQL query.

For example, if I were to accept a username and a password from a HTML form to be checked against a database of users, you may be tempted to do this:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. $name = $_POST['username'];
  3. $pwd = $_POST['password'];
  4.  
  5. $sql = "SELECT `UserID` FROM `User`
  6.         WHERE `UserName` = '{$name}'
  7.         AND `Password` = '{$pwd}'";
  8. $result = $mysql_query($sql) or die(mysql_error());
  9.  
  10. if($result && mysql_num_rows($result) > 0) {
  11.   echo "User is valid!";
  12. }
  13. else {
  14.   echo "User is invalid";
  15. }
  16. ?>
Fairly simple, right?
And this would work perfectly for typical users.

But consider what would happen if you were to pass this as the password:
Expand|Select|Wrap|Line Numbers
  1. ' OR 1='1
Now your query becomes:
Expand|Select|Wrap|Line Numbers
  1. SELECT `UserID` FROM `User`
  2. WHERE `UserName` = 'something'
  3. AND `Password` = '' OR 1='1'
Which would return every single row in the table and pass the validation, regardless of which username you passed.

The safest way to prevent this sort of tampering is to make sure the data passed does not contain any characters that might mess with the query.
Which is exactly what the mysql_real_escape_string function does.

If I were to change lines number 2 and 3 in the example above like so:
Expand|Select|Wrap|Line Numbers
  1. $name = mysql_real_escape_string($_POST['username']);
  2. $pwd = mysql_real_escape_string($_POST['password']);
And pass it the same bogus data I did before, this is the query that would get executed (from PHP's point of view):
Expand|Select|Wrap|Line Numbers
  1. SELECT `UserID` FROM `User`
  2. WHERE `UserName` = 'something'
  3. AND `Password` = '\' OR 1=\'1'
As you see, the quote-marks inside the Password field are now escaped, which means they are now a part of the value, rather than used to close the string and add an extra OR clause.

So, unless there is actually a user named 'something' using that password, the query would return 0 rows and the validation would fail.

Another simple way to lessen the risk of simple tampering like that is to group boolean expressions together. Consider if my query had looked like:
Expand|Select|Wrap|Line Numbers
  1. $sql = "SELECT `UserID` FROM `User`
  2.         WHERE (`UserName` = '{$name}')
  3.         AND (`Password` = '{$pwd}')";
Passing the bogus data, un-validated, into this would produce:
Expand|Select|Wrap|Line Numbers
  1. SELECT `UserID` FROM `User`
  2. WHERE (`UserName` = 'something')
  3. AND (`Password` = '' OR 1='1')
Which would also fail if the username is invalid.
Granted, this is far from being adequate by itself, but every little bit helps :]
Feb 5 '09 #2
thank you that was a big help its grate that you have taken the time to explain it to me and i know understand how it works

Thank you
Feb 5 '09 #3
Atli
5,058 Expert 4TB
I'm glad I could help :)
Feb 6 '09 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: Bã§TãRÐ | last post by:
I have been working on this particular project for a little over 2 weeks now. This product contains between 700-900 stored procedures to handle just about all you can imagine within the product. I...
1
by: Simon Wigzell | last post by:
Is it possible to "intercept" all calls to conn.execute and have them go to a checking routine that will either let the command go through or terminate it if it contains some illegal instructions?...
16
by: Michael Kujawa | last post by:
Hi All, I have been given a site to redo. In the process of looking at the code, the live site is open to SQL injection. I know what needs to be done but limited time right now to redo correctly....
8
by: stirrell | last post by:
Hello, One problem that I had been having is stopping email injections on contact forms. I did some research, read up on it and felt like I had created a working solution. I hadn't gotten any...
1
by: Doug | last post by:
Hi, I have a question on sql injection attacks. I am building a tool that will be used exclusively by our other developers and will generate stored procs for them dynamically based off input...
7
by: | last post by:
There are assorted "SQL Injection vulnerability assessment tools" out there. They scan your site and send your report. They also take your money. We don't have the money so I was wondering if I...
2
by: Sudhakar | last post by:
A) validating username in php as part of a registration form a user fills there desired username and this is stored in a mysql. there are certain conditions for the username. a) the username...
12
by: shank | last post by:
I've been hit again using DW, parameterized queries and stored procedures. I'm guessing I was not strict enough with character counts and allowing to long of a string to pass. Aside from that,...
14
Frinavale
by: Frinavale | last post by:
I've been trying to test my web application using Internet Explorer 8 (release candidate 1) and have been experiencing some major problems. I'm hoping you can help me with this one. I have a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.