I am forced to generate dynamic SQL.  I realize it greatly complicates matters to do so, and the below example is silly and obviously does not require dynamic SQL, and is just used to illustrate this question.  Escaping user provided data is not enough, and the 3rd query in the below script is suspect to SQL Injection.  I have found that it is generally easiest to design the application so that all user inputs are integers, and simply typecast their input using (int)$_POST['user_input'].  I now have a need where the user_input needs to be text.  What should I do to prevent SQL injection?  One option is PHP's ctype_alpha(), however, I don't want "user,input" to result as FALSE, but should either remain as is or be converted to "userinput" (either scenerio is okay for me).  I am thinking of something like $user_input= preg_replace('/[^a-z0-9,]/i', '', $_POST['user_input']);.  Will this be 100% safe?  EDIT  Note that I am not executing the below query, but inserting it into a database, and as such, prepared statements are not appliable.  You might believe that dynamic SQL generation should never be used, but telling me to use prepared statements is not right.  &lt;?php $id=123; $query='SELECT * FROM someTable WHERE someColumn="'.$_POST['user_input'].'"'; $sql='INSERT INTO meta_table (id,sql) VALUES (?,?)'; $stmt=db::db()-&gt;prepare($sql); $stmt-&gt;execute(array($id,$query));  $sql='SELECT sql FROM meta_table WHERE id=123'; $stmt = db::db()-&gt;exec($sql); $query=$stmt-&gt;fetchColumn();  $stmt = db::db()-&gt;exec($query); //Prone to SQL Injection $rs=$stmt-&gt;fetchColumn(); ?&gt;
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?
108771
In the below scenario  i am Accepting the ID of object in the string form : What will be the best way to avoid the SQL injection for ASId.  public static List&lt;AB__c&gt;ABC(string ASId)         records = [select id, name,abc__c,der__f, from AB__c WHERE der__f = :ASId WITH SECURITY ENFORCED ORDER BY AB__c ASC NULLS LAST];
1681
When using SQL, are there any benefits of using = in a WHERE clause instead of LIKE?  Without any special operators, LIKE and = are the same, right?
14196
I realize that CHAR is recommended if all my values are fixed-width. But, so what? Why not just pick VARCHAR for all text fields just to be safe.
9564
I am looking to return a list of users from a database. There are hundreds of users in the db. Each user has it's own unique user_id. All of the values that I am trying to return reside in the same column (meta_value).   The database structure is as follows:  id | user_id | meta_key | meta_value   sample data is as follows:   1 | 3434 | first_name | Brandon 2 | 3434 | last_name  | Johnson 3 | 3434 | street_add | 123 main 4 | 3434 | city       | ocean beach 5 | 3434 | state      | Texas   I am trying to return the first name, last name, street_add, city, and state in one sql statement.  I'd like the output to look like:  Brandon, Johnson, 123 Main, Ocean Beach, Texas
20284
I'm supposed to test a web application for SQL injections. I'm new to this type of security testing and I'd like to get some tips on the matter. Are there any best practices or do I simply search on the Internet for the most common SQL injections and try them out on my application?  I hope some guidance would be profitable for other newbies like me, so please, help me complete the following lists:   List of "places" in the application to be tested:   username  password(s)  e-mail search field  text fields any other fields that are available to the user  List of tests to be performed:   ...?    Thanks!
114524
How do I generate a range of consecutive numbers (one per line) from a MySQL query so that I can insert them into a table?  For example:  nr 1 2 3 4 5   I would like to use only MySQL for this (not PHP or other languages).
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?
34548
I'm trying to make a class that will execute any one of a number of stored procedures with any amount of variables  Im using php and mysqli   My class enumerates an array and constructs a string based on the number of elements if any giving something like this CALL spTestLogin(?,?) for example I now need to bind the input from my array using somethin like this:  $stmt->bind_param($this->paramTypes,$this->paramValues);//paramValues is my array   Then if that works I can work on getting my results  Any ideas
48211
Im using smarty and mysql_real_escape_string() for user input, and when I insert some code with ' or " , and lookup in phpmyadmin it shows without backslashes.  When I get record from DB i doesn't have backslashes also. But when I just pass escaped string without inserting into the db it is backslashed.  Shouldn't it add slashes, insert with them and then I would  strip them when i would output? Or am I missing something?
53962
I have a pretty active discussion on SO right now, about giving an answer, that could potentially harm the questioner. The question is  and discussion is in comments to .   In short, the OP asks for a way to implement a really dangerous thing. I've provided an answer to the question with a lot of warnings, while  told me that this is a very bad idea, and it is better to use prepared statements, and so on. He is absolutely right; I'm among those who upvoted his answer, but the question remains.   So, I would like to hear community's opinion on that topic. Should we protect others from shooting in their legs, or let them have responsibility to do it, if they want?  Update: One of my university teachers created  site (long ago). The idea of that site is to allow people training their SQL skills. I won't tell much about the site, you can register and check it out yourself (it is available in english), but the whole idea is just the same: execute SQL provided by the user. Of course it is fltered, checked that it is safe, placed in a sandbox, etc. But the concept is just the same. It's rather popular resource, it is on third place in  and have large and growing community. I hope this update will stimulate further discussion.
55771
Anyone know how to combine PHP prepared statements with LIKE? i.e.   "SELECT * FROM table WHERE name LIKE %?%";
119920
I'm working on an application for work that is going to query our employee database. The end users want the ability to search based on the standard name/department criteria, but they also want the flexibility to query for all people with the first name of "James" that works in the Health Department. The one thing I want to avoid is to simply have the stored procedure take a list of parameters and generate a SQL statement to execute, since that would open doors to SQL injection at an internal level.  Can this be done?
7387
Can someone explain SQL injection?  How does it cause vulnerabilities?  Where exactly is the point where SQL is injected?
102930
I will preface this question by saying, I do not think it is solvable. I also have a workaround, I can create a stored procedure with an OUTPUT to accomplish this, it is just easier to code the sections where I need this checksum using a function.  This code will not work because of the Exec SP_ExecuteSQL @SQL calls. Anyone know how to execute dynamic SQL in a function? (and once again, I do not think it is possible. If it is though, I'd love to know how to get around it!)  Create Function Get_Checksum (     @DatabaseName      varchar(100),     @TableName         varchar(100) ) RETURNS FLOAT AS BEGIN   Declare @SQL        nvarchar(4000)  Declare @ColumnName varchar(100)  Declare @i          int  Declare @Checksum   float  Declare @intColumns table (idRecord int identity(1,1), ColumnName varchar(255))  Declare @CS         table (MyCheckSum bigint)   Set @SQL =          'Insert Into @IntColumns(ColumnName)' + Char(13) +          'Select Column_Name' + Char(13) +         'From   ' + @DatabaseName + '.Information_Schema.Columns (NOLOCK)' + Char(13) +         'Where  Table_Name = ''' + @TableName + '''' + Char(13) +         '       and Data_Type = ''int'''    -- print @SQL   exec sp_executeSql @SQL   Set @SQL =          'Insert Into @CS(MyChecksum)' + Char(13) +          'Select '   Set @i = 1   While Exists(        Select 1        From   @IntColumns        Where  IdRecord = @i)  begin        Select @ColumnName = ColumnName        From   @IntColumns        Where  IdRecord = @i         Set @SQL = @SQL + Char(13) +              CASE WHEN @i = 1 THEN                   '    Sum(Cast(IsNull(' + @ColumnName + ',0) as bigint))'                  ELSE                  '    + Sum(Cast(IsNull(' + @ColumnName + ',0) as bigint))'             END         Set @i = @i + 1  end   Set @SQL = @SQL + Char(13) +        'From ' + @DatabaseName + '..' + @TableName + ' (NOLOCK)'   -- print @SQL   exec sp_executeSql @SQL   Set @Checksum = (Select Top 1 MyChecksum From @CS)   Return isnull(@Checksum,0)  END GO
5230
Here's my attempt at it:  $query = $database-&gt;prepare('SELECT * FROM table WHERE column LIKE "?%"');  $query-&gt;execute(array('value'));  while ($results = $query-&gt;fetch())  {     echo $results['column']; }
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.