Good morning all,  I recently made a post on this website regarding a login page for a website. It was pointed out to me that I was very vulnerable to an SQL injection. I have spent the past weekend researching into SQL injections and I am getting a bit of a better idea about how they function, however I am still very new to PHP (I taught myself the basics in a day). I was wondering if anyone could help me with my code please.   I have read every link that people posted and researched it till my head exploded (no need to edit, its not literal), but I am still struggling as to the code itself.   Here is my code:  &lt;?php     session_start();     // dBase file     include "dbConfig.php";      if ($_GET["op"] == "login")     {        if (!$_POST["username"] || !$_POST["password"])        {          die("You need to provide a username and password.");        }         // Create query        $q = "SELECT * FROM `dbusers` "        ."WHERE `username`='".$_POST["username"]."' "        ."AND `password`=PASSWORD('".$_POST["password"]."') "        ."LIMIT 1";        // Run query        $r = mysql_query($q);        if ( $obj = @mysql_fetch_object($r) )       {         // Login good, create session variables         $_SESSION["valid_id"] = $obj-&gt;id;         $_SESSION["valid_user"] = $_POST["username"];         $_SESSION["valid_time"] = time();          // Redirect to member page         Header("Location: members.php");       }       else       {         // Login not successful         die("Sorry, could not log you in. Wrong login information.");       }    }    else    {     //If all went right the Web form appears and users can log in     echo "&lt;form action=\"?op=login\" method=\"POST\"&gt;";     echo "Username: &lt;input name=\"username\" size=\"15\"&gt;&lt;br /&gt;";     echo "Password: &lt;input type=\"password\" name=\"password\" size=\"8\"&gt;&lt;br /&gt;";     echo "&lt;input type=\"submit\" value=\"Login\"&gt;";     echo "&lt;/form&gt;";    }  ?&gt;   Now I know it needs validation AND sanitisation in PDO, am just struggling as to what to actually write in my code. I am hoping that someone could help rather than just link me to another page please  as an edit, if anyone has a link to a tutorial about logins which are SQLinjection safe that could help me/other people looking to protect against that would be much appreciated. Thanks
73
If user input is inserted without modification into an SQL query, then the application becomes vulnerable to , like in the following example:  $unsafe_variable = $_POST['user_input'];   mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");   That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:  INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')   What can be done to prevent this from happening?
15587
I have a query to insert a row into a table, which has a field called ID, which is populated using an AUTO_INCREMENT on the column. I need to get this value for the next bit of functionality, but when I run the following, it always returns 0 even though the actual value is not 0:  MySqlCommand comm = connect.CreateCommand(); comm.CommandText = insertInvoice; comm.CommandText += "\'" + invoiceDate.ToString("yyyy:MM:dd hh:mm:ss") + "\', " + bookFee + ", " + adminFee + ", " + totalFee + ", " + customerID +  ")"; int id = Convert.ToInt32(comm.ExecuteScalar());   According to my understanding, this should return the ID column, but it just returns 0 every time. Any ideas?  EDIT:  When I run:  "INSERT INTO INVOICE (INVOICE_DATE, BOOK_FEE, ADMIN_FEE, TOTAL_FEE, CUSTOMER_ID) VALUES ('2009:01:01 10:21:12', 50, 7, 57, 2134);last_insert_id();"   I get:  {"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'last_insert_id()' at line 1"}
27610
I'm receiving the following warnings when I use mysql_query() in my modules:  mysql_query(): A link to the server could not be established in ...... on line 6  If I use db_query() instead, it works fine.   The code is a simple SELECT query with no special characters or variables. The actual query is mysql_query("SELECT * FROM custom_nodify WHERE nid = 345");  I should mention that this happens with ANY mysql_query(), not just the one above.  Any suggestions as to why this doesn't work?
110550
For a new project that I'm doing in PHP I've created an SQLMethods class to connect to the database and perform queries. Tonight was the first night that I actually got to test it (I wrote it a week or so ago and forgot about it) and an unexpected error occured: When it was calling my ExecuteQuery() function, it wouldn't use the database I selected in the constructor.  The constructor:      public function SQLMethods() {         $SQLConnection = mysql_connect($SQLDBAddress, $SQLUserName, $SQLPassword);          if (!$SQLConnection) {             die('Could not connect: ' . mysql_error());         }          mysql_select_db($SQLDB, $SQLConnection);     }   The function in question:      public function ExecuteQuery($Query) {         mysql_query($Query, $SQLConnection) or die('Could not perform query: ' . mysql_error());     }   Does anyone see what the issue might be? Does the connection close after the constructor completes?
104519
Not sure if this belongs here or at serverfault...  I've seen websites where, to login to the website, requires a digital certificate to be installed for the user logging in. As far as I can tell, this certificate is in addition to the website using an SSL certificate (https)  I'm just looking to be pointed in the right direction on how to code for this (apache / php hopefully), who issues these certificates (must it be a trusted var or can I ?) or even what to search for via google.  -Mario
22164
I just can't login to PhpMyAdmin anymore, it says bad logins. PHP works perfectly. Please help. Thank you.  2002 Cannot log in to the MySQL server Language Log in Username: Password:  Connection for controluser as defined in your configuration failed.
96046
I am having a few issues when people are trying to access a MySQL database and they are trying to update tables with the same information.  I have a webpage written using PHP. In this webpage is a query to check if certain data has been entered into the database. If the data hasn't, then i proceed to insert it. The trouble is that if two people try at the same time, the check might say the data has not been entered yet but when the insert takes place it has been by the other person.  What is the best way to handle this scenario? Can i lock the database to only process my queries first then anothers?
31406
I am planning to replace repeatedly executed Statement objects with PreparedStatement objects to improve performance. I am using arguments like the MySQL function now(), and string variables.   Most of the PreparedStatement queries I have seen contained constant values (like 10, and strings like "New York") as arguments used for the ? in the queries. How would I go about using functions like now(), and variables as arguments? Is it necessary to use the ?s in the queries instead of actual values? I am quite confounded.
40449
I'm creating an installation script for an application that I'm developing and need to create databases dynamically from within PHP. I've got it to create the database but now I need to load in several .sql files. I had planned to open the file and mysql_query it a line at a time - until I looked at the schema files and realised they aren't just one query per line.  So, how do I load an sql file from within PHP (as phpMyAdmin does with its import command)?
6735
I've got a working PHP script that gets Longitude and Latitude values and then inputs them into a MySQL query. I'd like to make it solely MySQL. Here's my current PHP Code:  if ($distance != "Any" &amp;&amp; $customer_zip != "") { //get the great circle distance      //get the origin zip code info     $zip_sql = "SELECT * FROM zip_code WHERE zip_code = '$customer_zip'";     $result = mysql_query($zip_sql);     $row = mysql_fetch_array($result);     $origin_lat = $row['lat'];     $origin_lon = $row['lon'];      //get the range     $lat_range = $distance/69.172;     $lon_range = abs($distance/(cos($details[0]) * 69.172));     $min_lat = number_format($origin_lat - $lat_range, "4", ".", "");     $max_lat = number_format($origin_lat + $lat_range, "4", ".", "");     $min_lon = number_format($origin_lon - $lon_range, "4", ".", "");     $max_lon = number_format($origin_lon + $lon_range, "4", ".", "");     $sql .= "lat BETWEEN '$min_lat' AND '$max_lat' AND lon BETWEEN '$min_lon' AND '$max_lon' AND ";     }   Does anyone know how to make this entirely MySQL? I've browsed the Internet a bit but most of the literature on it is pretty confusing.
106111
I have already uploaded some files in the free web host I chose and created a database there using phpMyAdmin v2.11.4  Also, I have Googled first this question before posting this here but I don't understand some rules and I need a clarification.  The exact error I get from viewing my uploaded file(index.php) is this:  Warning: mysqli_connect() [function.mysqli-connect]: (HY000/1130): Host '31.170.160.98' is not allowed to connect to this MySQL server in /home/a6400350/public_html/subdomains/index.php on line 7   Domain name of free web host I'm using is   And my php code for connecting to host is:  &lt;?php  $host = "sql10.000webhost.com"; //or "31.170.160.98" $username = "a6400350xxxxxx"; $password = "••••••••";  $con = mysqli_connect($host, $username, $password);  ?&gt;    I also check if the server is down  or it's just me There was also no previlege section in phpMyAdmin page as other suggest   Don't I have any permission to connect programmatically to this host as I am just a free user?  Edit     I'm also thinking of uploading the whole XAMPP in the server so I   could do the things I'm imagining.   Edit 2     Also one more thing, I imported the database by means of .sql file   locally through that server, maybe this has something to do about it?
86789
I don't have this kind of ID in my table, still MySQL is giving me a result.  This same result is giving when i place in my php code.
62599
Possible Duplicate:          I'm using the mysqli extension in PHP and I'm wondering, is there possibly any way to see a prepared query as it will be executed on the server, e.g. The query is something like this  select * from table1 where id = ? and name = ?   but I want to see the query after the values are filled in, like this:  select * from table1 where id = 20 and name = "John"
6749
What are the best workarounds for using a SQL IN clause with instances of java.sql.PreparedStatement, which is not supported for multiple values due to SQL injection attack security issues: One ? placeholder represents one value, rather than a list of values.  Consider the following SQL statement:  SELECT my_column FROM my_table where search_column IN (?)   Using preparedStatement.setString( 1, "'A', 'B', 'C'" ); is essentially a non-working attempt at a workaround of the reasons for using ? in the first place.    What workarounds are available?
31361
Is there a way to configure a password for a stored session in ?  I know there is the capability to specify an "auto-login username" (under Connection/Data), but is there a way to do the same with the password?
47691
I've heard that "everyone" is using parameterized SQL queries to protect against SQL injection attacks without having to vailidate every piece of user input.  How do you do this?  Do you get this automatically when using stored procedures?  So my understanding this is non-parameterized:    cmdText = String.Format("SELECT foo FROM bar WHERE baz = '{0}'", fuz)   Would this be parameterized?  cmdText = String.Format("EXEC foo_from_baz '{0}'", fuz)   Or do I need to do somethng more extensive like this in order to protect myself from SQL injection?  With command     .Parameters.Count = 1     .Parameters.Item(0).ParameterName = "@baz"     .Parameters.Item(0).Value = fuz End With   Are there other advantages to using parameterized queries besides the security considerations?  Update:  This great article was linked in one of the questions references by Grotok.
99589
I am stuck on the prevent SOQL injection trailhead, where I am looking to determine which portions of the code I would need to invoke escapeSingleQuotes or whitelist to enable. I've made several attempts at investigation to little avail. I have appended my code are there any steps I would need to take. My goal is to assess the code to see how it works and what I would need to do.  public class Prevent_SOQL_Injection_Challenge {  public string textOne {get; set;} public string textTwo {get; set;} public string comparator {get; set;} public string numberOne {get; set;}  public List&lt;Supply__c&gt; whereclause_records {get; set;}   public PageReference stringSearchOne(){     string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';     string whereClause = '';      if(textOne != null &amp;&amp; textOne!=''){             whereClause += 'name like  \'%'+string.escapeSingleQuotes(textOne)+'%\' ';     }      if(whereClause != ''){         whereclause_records = database.query(query+' where '+whereClause+' Limit 10');     }      return null; }   public PageReference stringSearchTwo(){     string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';     string whereClause = '';      if(textTwo != null &amp;&amp; textTwo!=''){             whereClause += 'Storage_Location__r.name like  \'%'+string.escapeSingleQuotes(textTwo)+'%\' ';     }      if(whereClause != ''){         whereclause_records = database.query(query+' where '+whereClause+' Limit 10');     }      return null; }   public PageReference numberSearchOne(){     string query = 'SELECT Id,Name,Quantity__c,Storage_Location__c,Storage_Location__r.Castle__c,Type__c FROM Supply__c';     string whereClause = '';      if(numberOne != null &amp;&amp; comparator != null){         whereClause += 'Quantity__c '+comparator+' '+string.ValueOf(numberOne)+' ';     }      if(whereClause != ''){         whereclause_records = database.query(query+' where '+string.ValueOf(whereClause)+' Limit 10');     }      return null; } }