Obviously this SQL statement is vulnerable:  Dim sql As String = "select * from person where fname = '" &amp; fname &amp; "'"   But what of this?  Dim sql as string = "select * from person where fname = '" &amp; fname.Replace("'", "''") &amp; "'"   Ideally we'd use SQL parameters but I'm wondering if the latter style of SQL statement is still safe or if I need to fix these as well...
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.
73953
I have been working with T-SQL in MS SQL for some time now and somehow whenever I have to insert data into a table I tend to use syntax:  INSERT INTO myTable &lt;something here&gt;   I understand that keyword INTO is optional here and I do not have to use it but somehow it grew into habit in my case.  My question is:    Are there any implications of using INSERT syntax versus INSERT INTO? Which one complies fully with the standard? Are they both valid in other implementations of SQL standard?
29504
What is the best way to replace all '&amp;lt' with &amp;lt; in a given database column? Basically perform s/&amp;lt[^;]/&amp;lt;/gi  Notes:   must work in  2000 Must be repeatable (and not end up with &amp;lt;;;;;;;;;;)
108487
I have the code below (I've included what I believe are all relevant sections):  private String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = ? AND VAL_@ = ?;"; public bool read(string id) {     level = -1;     MySqlCommand m = new MySqlCommand(readCommand);     m.Parameters.Add(new MySqlParameter("", val1));     m.Parameters.Add(new MySqlParameter("", val2));     MySqlDataReader r = m.ExecuteReader();     if (r.HasRows)         level = Convert.ToInt32(r.GetValue(0).ToString());     r.Close();     return true; }   When I run this, I get an IndexOutOfBoundsException on adding the first parameter. What have I done wrong?
36213
I'm a bit stuck on this. Basically I want to do something like the following SQL query in LINQ to SQL: SELECT f.*  FROM Foo f WHERE f.FooId IN (     SELECT fb.FooId     FROM FooBar fb     WHERE fb.BarId = 1000 )  Any help would be gratefully received.
4991
Using SQLite3 with Python 2.5, I'm trying to iterate through a list and pull the weight of an item from the database based on the item's name.  I tried using the "?" parameter substitution suggested to prevent SQL injections but it doesn't work. For example, when I use:  for item in self.inventory_names:     self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", item)     self.cursor.close()   I get the error:     sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 8 supplied.   I believe this is somehow caused by the initial creation of the database; the module I made that actually creates the DB does have 8 bindings.  cursor.execute("""CREATE TABLE Equipment      (id INTEGER PRIMARY KEY,      name TEXT,     price INTEGER,      weight REAL,      info TEXT,      ammo_cap INTEGER,      availability_west TEXT,     availability_east TEXT)""")   However, when I use the less-secure "%s" substitution for each item name, it works just fine. Like so:  for item in self.inventory_names:     self.cursor.execute("SELECT weight FROM Equipment WHERE name = '%s'" % item)     self.cursor.close()   I can't figure out why it thinks I have 8 bindins when I'm only calling one. How can I fix it?
21568
$sql = "SELECT Kill FROM tbl_pvporderview";   Problem is that I end up with: Incorrect syntax near the keyword 'Kill'.  Because kill is a T-SQL command... any way to bypass it?  I can't change the column name because it's used by the software a lot and I cant change the software that's using the database.  So it simply fails if I use sqlserv to select data from that column. '' or "" wont help.  The complete statement would be:  $sql = "SELECT serial,Kill FROM tbl_pvporderview WHERE Kill &gt; (?) ORDER BY Kill DESC ";
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().
10807
maybe there is a simple answer to my problem:  I have a mssql table ('Person') with a field for first and one for last name. I am coding a Program, where the User should be able to search for persons.     SELECT id, firstname, lastname FROM Person WHERE firstname LIKE   @searchstring + '%' OR lastname LIKE @searchstring + '%'   doesn't work very well, because if the user types 'John W' nothing is found.  For example the person I search for is called John Williams: It should work with searchstrings like 'John W', 'Williams Jo', 'Will' or 'Joh'  Concatenating first and lastname and use %Searchstring% also doesn't work well, because if the searchstring is e.g. 'Will' it should find people who are called Will, Willibald, Williams or Willson or something like that, but not a Person called Goodwill e.g.  And the query should be as fast as possible, because it is executed at every keydown...  edit: forgot to mention that the database must not be changed, because it is from another company.  Thank you for your help.
23076
How do I go from this string: "ThisIsMyCapsDelimitedString"  ...to this string: "This Is My Caps Delimited String"  Fewest lines of code in VB.net is preferred but C# is also welcome.  Cheers!
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?
43699
I want to generate some formatted output of data retrieved from an MS-Access database and stored in a DataTable object/variable, myDataTable. However, some of the fields in myDataTable cotain dbNull data. So, the following VB.net code snippet will give errors if the value of any of the fields lastname, intials, or sID is dbNull.     dim myDataTable as DataTable    dim tmpStr as String    dim sID as Integer = 1     ...    myDataTable = myTableAdapter.GetData() ' Reads the data from MS-Access table    ...     For Each myItem As DataRow In myDataTable.Rows      tmpStr = nameItem("lastname") + " " + nameItem("initials")      If myItem("sID")=sID Then         ' Do something     End If      ' print tmpStr     Next   So, how do i get the above code to work when the fields may contain dbNull without having to check each time if the data is dbNull as in ?
79438
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?  The naive approach would be to inspect each record, executing a regular expression or partial string match on the field of interest, but I have a feeling (hope) that these systems do something smarter.
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?
63457
Is there a way to mix LIKE operator with IN ? Something Like  SELECT Id FROM Account WHERE Name LIKE IN:nameList   Where nameList is list of String? Is this is possible ? (I can make a dynamic query by doing OR with all the elements, but was looking for a more elegant way to do this)
59128
I am writing a stored procedure where I have an input parameter called my_size that is an INTEGER. I want to be able to use it in a LIMIT clause in a SELECT statement. Apparently this is not supported, is there a way to work around this?  # I want something like: SELECT * FROM some_table LIMIT my_size;  # Instead of hardcoding a permanent limit: SELECT * FROM some_table LIMIT 100;
9044
I am coding in ColdFusion, but trying to stay in cfscript, so I have a function that allows me to pass in a query to run it with   &lt;cfquery blah >   #query# &lt;/cfquery>   Somehow though, when I construct my queries with sql = "SELECT * FROM a WHERE b='#c#'"  and pass it in, ColdFusion has replaced the single quotes with 2 single quotes.  so it becomes  WHERE b=''c'' in the final query.  I have tried creating the strings a lot of different ways, but I cannot get it to leave just one quote.  Even doing a string replace has no effect.   Any idea why this is happening?  It is ruining my hopes of living in cfscript for the duration of this project