I am very new to mysqli earlier i am writing queries in mysql but mysqli is more advanced so, i am first time using it. Below is my php code.       function clean($str) {         $str = @trim($str);         if(get_magic_quotes_gpc()) {             $str = stripslashes($str);         }         return mysql_real_escape_string($str);     }          $email = clean($_POST['email']);         $password = clean($_POST['password']);         //$password =md5($password);       if(empty($res['errors'])) {         $result = $mysqli-&gt;query("SELECT uid FROM users where email='$email' and password = '$password'");         if($result-&gt;num_rows == 1){             $res['success'] = true;         }         else{             array_push($res['errors'], 'Invalid login details');             $res['success'] = false;          }     }else{         $res['success'] = false;             }     echo json_encode($res); }   clean function is not working as expected because sql queries return false if i enter username and password correct. So, it seems like this is not valid in mysqli case.  I checked this link  and got to know that we have to prepare query.   I can see there is an example but i am not able to understand how to prepare/bind if i have to use two or more form data.  Thanks for your time.  Updated code  $result = $mysqli-&gt;prepare("SELECT uid FROM users where email=:email and password = :password");         $result-&gt;execute([ ':email' =&gt; $email,          ':password' =&gt; $password]);         //$result-&gt;execute();         if($result-&gt;num_rows == 1){         //if(mysqli_num_rows($result) === 1) {             $res['success'] = true;          }         else{             array_push($res['errors'], 'Invalid login details');             $res['success'] = false;          }
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?
12526
Would you recommend using a  or a  field, and why (using MySQL)?   I'm working with PHP on the server side.
73749
I am doing a tutorial and am getting this error:     Fatal error: Class 'MySQLi' not found (LONG URL) on line 8   The code on line 8 is:  $mysqli = new MySQLi($db_server, $db_user, $db_pass, $db_name);   I saw online someone said to see if it was turned on in my phpinfo(), but there wasn't anything listed in there under for "mysqli".  Also, I am running PHP version 5.2.5
53567
I have a query which is meant to show me any rows in table A which have not been updated recently enough. (Each row should be updated within 2 months after "month_no".):  SELECT A.identifier      , A.name      , TO_NUMBER(DECODE( A.month_no              , 1, 200803               , 2, 200804               , 3, 200805               , 4, 200806               , 5, 200807               , 6, 200808               , 7, 200809               , 8, 200810               , 9, 200811               , 10, 200812               , 11, 200701               , 12, 200702              , NULL)) as MONTH_NO      , TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE   FROM table_a A      , table_b B  WHERE A.identifier = B.identifier    AND MONTH_NO &gt; UPD_DATE   The last line in the WHERE clause causes an "ORA-00904 Invalid Identifier" error. Needless to say, I don't want to repeat the entire DECODE function in my WHERE clause. Any thoughts? (Both fixes and workarounds accepted...)
74983
I'm writing a php app to access a MySQL database, and on a tutorial, it says something of the form  mysql_connect($host, $user, $pass) or die("could not connect");   How does PHP know that the function failed so that it runs the die part? I guess I'm asking how the "or" part of it works. I don't think I've seen it before.
61016
I realize that parameterized SQL queries is the optimal way to sanitize user input when building queries that contain user input, but I'm wondering what is wrong with taking user input and escaping any single quotes and surrounding the whole string with single quotes. Here's the code:  sSanitizedInput = "'" &amp; Replace(sInput, "'", "''") &amp; "'"   Any single-quote the user enters is replaced with double single-quotes, which eliminates the users ability to end the string, so anything else they may type, such as semicolons, percent signs, etc., will all be part of the string and not actually executed as part of the command.    We are using Microsoft SQL Server 2000, for which I believe the single-quote is the only string delimiter and the only way to escape the string delimiter, so there is no way to execute anything the user types in.  I don't see any way to launch an SQL injection attack against this, but I realize that if this were as bulletproof as it seems to me someone else would have thought of it already and it would be common practice.    What's wrong with this code? Is there a way to get an SQL injection attack past this sanitization technique?  Sample user input that exploits this technique would be very helpful.    UPDATE:  I still don't know of any way to effectively launch a SQL injection attack against this code. A few people suggested that a backslash would escape one single-quote and leave the other to end the string so that the rest of the string would be executed as part of the SQL command, and I realize that this method would work to inject SQL into a MySQL database, but in SQL&nbsp;Server 2000 the only way (that I've been able to find) to escape a single-quote is with another single-quote; backslashes won't do it.  And unless there is a way to stop the escaping of the single-quote, none of the rest of the user input will be executed because it will all be taken as one contiguous string.  I understand that there are better ways to sanitize input, but I'm really more interested in learning why the method I provided above won't work. If anyone knows of any specific way to mount a SQL injection attack against this sanitization method I would love to see it.
26532
I'm trying to write a page that calls PHP that's stored in a MySQL database. The page that is stored in the MySQL database contains PHP (and HTML) code which I want to run on page load.  How could I go about doing this?
112694
What mysql functions are there (if any) to trim leading zeros from an alphanumeric text field?    Field with value "00345ABC" would need to return "345ABC".
74148
Does anyone know of a program, a utility, or some programmatic library, preferably for Linux, that takes an unformatted SQL string and pretty prints it?  For example I would like the following  select * from users where name = 'Paul'   be changed to something like this  select *  from users where    name = 'Paul'   The exact formatting is not important. I just need something to take a large SQL string and break it up into something more readable.
39428
I'm looking to find records in a table that match a specific number that the user enters.  So, the user may enter 12345, but this could be 123zz4-5 in the database. I imagine something like this would work, if PHP functions worked in MySQL. SELECT * FROM foo WHERE preg_replace(&quot;/[^0-9]/&quot;,&quot;&quot;,bar) = '12345'  What's the equivalent function or way to do this with just MySQL? Speed is not important.
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?
10993
In our place we're split between using mysqli and PDO for stuff like prepared statements and transaction support. Some projects use one, some the other. There is little realistic likelihood of us ever moving to another RDBMS.  I prefer PDO for the single reason that it allows named parameters for prepared statements, and as far as I am aware mysqli does not.  Are there any other pros and cons to choosing one over the other as a standard as we consolidate our projects to use just one approach?
9353
I have a loop that is doing some error checking in my PHP code. Originally it looked something like this...  foreach($results as $result) {     if (!$condition) {         $halt = true;         ErrorHandler::addErrorToStack('Unexpected result.');     }      doSomething();  }  if (!$halt) {     // do what I want cos I know there was no error }   This works all well and good, but it is still looping through despite after one error it needn't. Is there a way to escape the loop?
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"}
89206
I am getting error:     The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal   ...while trying to pass variable as parameter to JSON_QUERY function. Please help me to resolve it.  DECLARE @cnt INT = 1; DECLARE @cnt_total INT = 1; DECLARE @json NVARCHAR(MAX); DECLARE @json1 NVARCHAR(MAX); declare @str  VARCHAR(200);  WHILE @cnt &lt;= 10 BEGIN     set @str = '$.Seasons[0].Products['+convert(varchar,@cnt)+'].ProductChannels';     set @str = char(39) +@str+ char(39);     PRINT @STR     select @json = json_query(jfile,@STR) from Import.tstjson; --MORE CODE         SET @cnt = @cnt + 1; END   Sample data:  {   "Seasons": [     {       "Season": "12321231",       "Products": [         {           "ProductId": "211",           "ProductChannels": [             {               "ChannelId": 1,               "WeekQuantities": []             }           ],           "ccc": 3,           "Tttt": 4         }       ]     }   ] }
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.
51549
What's wrong with this query: INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;  It works without the WHERE clause. I've seemed to have forgot my SQL.