I am currently working on the store page for my website. I am connecting  to my database where data for the products is stored. The page has no user input, it just displays the data such as product name, price etc. My question is, can/should I implement prepared statements in order to prevent from SQL injection. If I can't use prepared statements are there any other forms of security I should implement in my website? Here is my current code:     &lt;?php  $connect = mysqli_connect('localhost', 'root','', 'cart'); $query = 'SELECT * FROM products ORDER by id ASC'; $result = mysqli_query($connect, $query);  if ($result):     if(mysqli_num_rows($result)&gt;0):         while($product = mysqli_fetch_assoc($result)):         ?&gt;                       &lt;div class=&quot;col-sm-4 col-md-3&quot; &gt;             &lt;form method=&quot;post&quot; action=&quot;view_product.php?action=add&amp;id=&lt;?php echo $product['id']?&gt;&quot;&gt;                 &lt;div class=&quot;products&quot;&gt;&lt;a href=&quot;view_product.php?product=&lt;?php echo $product['id'] ?&gt;&quot;&gt;                     &lt;img src=&quot;&lt;?php echo $product['image'];?&gt;&quot; /&gt;                     &lt;/a&gt;                     &lt;h4 class=&quot;text-dark&quot;&gt;&lt;a href=&quot;view_product.php?product=&lt;?php echo $product['id'] ?&gt;&quot;&gt;                         &lt;?php echo $product['name'];?&gt;                     &lt;/a&gt;                     &lt;/h4&gt;                     &lt;h4&gt;&lt;?php echo $product['price'];?&gt;&lt;/h4&gt;                 &lt;/div&gt;             &lt;/form&gt;         &lt;/div&gt;         &lt;?php                     endwhile;                 endif;           endif;             ?&gt;  When clicking on a certain product it redirects to another page where additional info is displayed for the particular product. I am using a query for that, could I use prepared statements for it? &lt;?php $query = 'SELECT * FROM products WHERE id = &quot;'.$_GET['product'].'&quot;'; ?&gt;  Thank you in advance!
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?
17495
I have the following code:  $countQuery = "SELECT ARTICLE_NO FROM ? WHERE upper(ARTICLE_NAME) LIKE '% ? %'"; if ($numRecords = $con-&gt;prepare($countQuery)) {     $numRecords-&gt;bind_param("ss", $table, $brand);     $numRecords-&gt;execute();     $data = $con-&gt;query($countQuery) or die(print_r($con-&gt;error));     $rowcount = mysql_num_rows($data);     $rows = getRowsByArticleSearch($query, $table, $max);     $last = ceil($rowcount/$page_rows); }   Which should work fine. However I receive the error that :  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 '? WHERE upper(ARTICLE_NAME) LIKE '%?%'' at line 1  If I put  SELECT ARTICLE_NO FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE '% o %';   The query works fine. $table is defined above, and query is received from GET, and both are correct valid values. Why is this failing?  edit: changing to:  $countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE '% ? %'";  if ($numRecords = $con-&gt;prepare($countQuery)) {      $numRecords-&gt;bind_param("s", $query);   results in the error:   Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of variables doesn't match number of parameters in prepared statement in C:\Program Files\EasyPHP 3.0\www\prog\get_records.php on line 38  Commands out of sync; you can't run this command now  where as   $countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE ?";  if ($numRecords = $con-&gt;prepare($countQuery)) {      $numRecords-&gt;bind_param("s", "%".$query."%");   results in  Fatal error: Cannot pass parameter 2 by reference in C:\Program Files\EasyPHP 3.0\www\prog\get_records.php on line 38  and lastly  $countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE ? ";  if ($numRecords = $con-&gt;prepare($countQuery)) {      $numRecords-&gt;bind_param("s", $query);   will give only:  Commands out of sync; you can't run this command now  Is it impossible to use a paramter for a LIKE statament?
91753
In a mysqli prepared statement, a NULL gets turned into '' (in the case of a string) or 0 (in the case of an integer). I would like to store it as a true NULL.  Is there any way of doing this?
89886
According to the , in order to make code more portable, they recommend using something like the following for escaping data:  if (!get_magic_quotes_gpc()) {     $lastname = addslashes($_POST['lastname']); } else {     $lastname = $_POST['lastname']; }   I have other validation checks that I will be performing, but how secure is the above strictly in terms of escaping data? I also saw that magic quotes will be deprecated in PHP 6. How will that affect the above code? I would prefer not to have to rely on a database-specific escaping function like mysql_real_escape_string().
77056
I have configured my MySQL Database to require passwords on all users, even root from the machine itself.  Now I discovered that there are empty Users in my Database  Reproduce: mysql -u root -p and then use mysql; &amp; select * from user;  It gives me 2 entries, one with "localhost" and user &lt;empty&gt; and one with the machine's hostname and &lt;empty&gt;.  Now I tried to access the account with mysql -u ' ' (yes it's correct, leave a space between the ' things) and I log in without a password. The user can "only" see information_schema and test, the two databases created by default. He does not has access to mysql or any other custom created databases.  I already changed the Password of this user to something I won't tell you  mysql -u root -p use mysql; UPDATE mysql.user SET Password=PASSWORD('thisisasecretpassword') WHERE USER='root'; FLUSH PRIVILEGES;   Now my Questions:   Is any MySQL Server vulnerable for an attack with this entry? Could an attacker break out of this account or the two default databases? Should I password-protect those Users or can I delete them? Are They required for some MySQL-internal things?
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?
29248
Background  I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in ).  Question  Is there a way that I can incorporate this select statement into a script that dumps the queried data into a new database, such that the database could be installed on a new mysql server, and the statement would work with the new database. The new database should not contain records in addition to those that have been used in the query.  Update: For clarification, I am not interested in a csv dump of query results. What I need to be able to do is to dump the database subset so that it can be installed on another machine, and then the query itself can be reproducible (and modifiable with respect to the same dataset).   Example  For example, my analysis might query a subset of data that requires records from multiple (in this example 3) tables:  select table1.id, table1.level, table2.name, table2.level         from table1 join table2 on table1.id = table2.table1_id         join table3 on table3.id = table2.table3_id        where table3.name in ('fee', 'fi', 'fo', 'fum');
53264
What are some guidelines for maintaining responsible session security with PHP?  There's information all over the web and it's about time it all landed in one place!
69918
So I have an SQL dump file that needs to be loaded using mysql_query(). Unfortunately, it's not possible to execute multiple queries with it.  -> It cannot be assumed that the mysql command-line client (mysql --help) is installed -- for loading the SQL file directly  -> It cannot be assumed that the mysqli extension is installed  /* contents of dump.sql, including comments */ DELETE FROM t3 WHERE body = 'some text; with semicolons; scattered; throughout'; DELETE FROM t2 WHERE name = 'hello'; DELETE FROM t1 WHERE id = 1;   The explode() below won't work because some of the dump content's values contain semicolons.  $sql = explode(';', file_get_contents('dump.sql')); foreach ($sql as $key =&gt; $val) {     mysql_query($val); }   What's the best way to load the SQL without modifying the dump file?
110185
I have recently inherited a codebase with a large amount of Stored Procedures.  The system they are supporting is encountering numerous performance problems which I am looking in to.  A number of the Stored Procedures have a pattern like this:   Create Temp table Build up dynamic SQL query to insert a bunch of records, e.g.  DECLARE @sql VARCHAR(MAX) SET @sql = 'INSERT INTO @tempTable SELECT SomeColumn, SomeColumn2, SomeColumn3, etc FROM MyTable'  IF @someParam = [SomeValue]     SET @sql = @sql + 'WHERE SomeColumn = [SomeValue]';  IF @someOtherParam = [SomeOtherValue]     SET @sql = @sql + 'WHERE SomeOtherColum = [SomeOtherValue]';  Execute this dynamic sql  EXEC(@sql);  Select from the temp table and bring in a bunch of additional information to return to the client.  SELECT     ...  FROM     @tempTable    INNER JOIN ...    My immediate thoughts are:   There is dynamic SQL, so no cache plans, meaning plans generated every time. There is an INSERT SELECT pattern, so table locking is more likely to be an issue.   I have re-written some of the Stored Procedures in this way instead:      SELECT          ...      FROM          MyTable          INNER JOIN ...      WHERE      (         @someParam != SomeValue         OR         SomeColumn = SomeValue     )     AND     (         @someOtherParam != SomeOtherValue         OR         SomeOtherColumn = SomeOtherValue     )           From comparing execution plans and client statistics in SQL Management Studio, I have not sped the Stored Procedures up so I am apprehensive about suggesting wholesale re-writes of all Stored Procedures.  I am trying to set up some profiling of a live customer scenario, but as yet have been unable to prove my thoughts.  Can anyone offer any confirmation of the theory behind my thoughts, or any better ways of proving my suspicions?  The problem is I have read that dynamic SQL is not always a closed case - i.e. it depends on how it is used.  My understanding of locking also falls down at the fact that nowhere can I get 100% confirmation of how this type of INSERT SELECT will lock tables.
94905
How do you build and use dynamic sql in a MySQL stored procedure?
117174
Problem:  Ajax suggest-search on [n] ingredients in recipes. That is: match recipes against multiple ingredients.  For instance: SELECT Recipes using "flower", "salt" would produce: "Pizza", "Bread", "Saltwater" and so forth.  Tables:  Ingredients [     IngredientsID INT [PK],     IngredientsName VARCHAR ]  Recipes [     RecipesID INT [PK],     RecipesName VARCHAR ]  IngredientsRecipes [     IngredientsRecipesID INT [PK],     IngredientsID INT,     RecipesID INT ]   Query:  SELECT     Recipes.RecipesID,     Recipes.RecipesName,     Ingredients.IngredientsID,     Ingredients.IngredientsName FROM     IngredientsRecipes      INNER JOIN Ingredients     ON IngredientsRecipes.IngredientsID = Ingredients.IngredientsID      INNER JOIN Recipes     ON IngredientsRecipes.RecipesID = Recipes.RecipesID WHERE     Ingredients.IngredientsName IN ('salt', 'water', 'flower')   I am currently constructing my query using ASP.NET C# because of the dynamic nature of the WHERE clause.  I bites that I have to construct the query in my code-layer instead of using a stored procedure/pure SQL, which in theory should be much faster.  Have you guys got any thoughts on how I would move all of the logic from my code-layer to pure SQL, or at least how I can optimize the performance of what I'm doing?  I am thinking along the lines of temporary tables:  Step one: SELECT IngredientsID FROM Ingredients and INSERT INTO temp-table  Step two: SELECT RecipesName FROM Recipes joined with IngredientsRecipes joined with temp-table.IngredientsID
111051
How would you write a prepared MySQL statement in PHP that takes a differing number of arguments each time? An example such query is:  SELECT `age`, `name` FROM `people` WHERE id IN (12, 45, 65, 33)   The IN clause will have a different number of ids each time it is run.  I have two possible solutions in my mind but want to see if there is a better way.  Possible Solution 1 Make the statement accept 100 variables and fill the rest with dummy values guaranteed not to be in the table; make multiple calls for more than 100 values.  Possible Solution 2 Don't use a prepared statement; build and run the query checking stringently for possible injection attacks.
58461
Consider the code:  PreparedStatement ps = null; ResultSet rs = null; try {   ps = conn.createStatement(myQueryString);   rs = ps.executeQuery();   // process the results... } catch (java.sql.SQLException e) {   log.error("an error!", e);   throw new MyAppException("I'm sorry. Your query did not work."); } finally {   ps.close();   rs.close(); }   The above does not compile, because both PreparedStatement.close() and ResultSet.close() throw a java.sql.SQLException.  So do I add a try/catch block to the finally clause?  Or move the close statements into the try clause?  Or just not bother calling close?
38502
I need to avoid being vulnerable to SQL injection in my ASP.NET application. How might I accomplish this?
17730
Is there a catchall function somewhere that works well for sanitizing user input for SQL injection and XSS attacks, while still allowing certain types of HTML tags?
40938
After I posted this question, I found a similar post that seemed to answer it: . I'll leave this in place for now, as I am not sure how to mark my own question as a duplicate  In attempting to debug a SOQL query error, I encountered a strange behavior with Database.query and bind variables. Specifically, when I run this code:  String userQuery = 'SELECT Id FROM User'; if (true) {     List&lt;String&gt; firstNames = new List&lt;String&gt;{'James','Joe'};     userQuery += ' WHERE FirstName IN :firstNames'; } Database.query(userQuery);   the Database.query statement executes with no errors, despite the fact that the variable firstNames does not exist in its context. However, when I add an else clause:  String userQuery = 'SELECT Id FROM User'; if (true) {     List&lt;String&gt; firstNames = new List&lt;String&gt;{'James','Joe'};     userQuery += ' WHERE FirstName IN :firstNames'; } else {     List&lt;String&gt; firstNames; } Database.query(userQuery);   I receive a null object reference exception. It seems like Salesforce is checking to see if the variable is only declared once beforehand and, if so, uses its value in the context it was declared. This seems strange to me, so I was wondering if anybody here has any insight into what is going on under the hood.