I need to pass a batch of parameters to mysql in python. Here is my code:  sql = """ SELECT * from my_table WHERE name IN (%s) AND id=%(Id)s AND puid=%(Puid)s"""  params = {'Id':id,'Puid'  : pid} in_p=', '.join(list(map(lambda x: '%s', names))) sql = sql %in_p  cursor.execute(sql, names) #todo: add params to sql clause   The problem is I want to pass the name list to sql IN clause, meanwhile I also want to pass the id and puid as parameters to the sql query clause. How do I implement these in python?
52772
I know how to map a list to a string:  foostring = ",".join( map(str, list_of_ids) )   And I know that I can use the following to get that string into an IN clause:  cursor.execute("DELETE FROM foo.bar WHERE baz IN ('%s')" % (foostring))   What I need is to accomplish the same thing SAFELY (avoiding SQL injection) using MySQLDB. In the above example because foostring is not passed as an argument to execute, it is vulnerable. I also have to quote and escape outside of the mysql library.   (There is a , but the answers listed there either do not work for MySQLDB or are vulnerable to SQL injection.)
31050
I need to run a query like:  SELECT p.id, p.name,         (SELECT name            FROM sites s           WHERE s.id = p.site_id) AS site_list   FROM publications p   But I'd like the sub-select to return a comma separated list, instead of a column of data.  Is this even possible, and if so, how?
62190
How can I create an Oracle stored procedure which accepts a variable number of parameter values used to feed a IN clause?  This is what I am trying to achieve. I do not know how to declare  in PLSQL for passing a variable list of primary keys of the rows I want to update.  FUNCTION EXECUTE_UPDATE   ( &lt;parameter_list&gt;    value IN int)   RETURN  int IS BEGIN      [...other statements...]     update table1 set col1 = col1 - value where id in (&lt;parameter_list&gt;)       RETURN SQL%ROWCOUNT ; END;   Also, I would like to call this procedure from C#, so it must be compatible with .NET capabilities.   Thanks, Robert
103055
Please look at this code:  create table #t1(   id int identity (1,1),   val varchar(10) );   insert into #t1 values ('a'); insert into #t1 values ('b'); insert into #t1 values ('c'); insert into #t1 values ('d');   Now, whenever you execute this  select *,      ( select top 1 val from #t1 order by NEWID()) rnd  from #t1 order by 1;   you will get a result with where all rows have the same random value. e.g.  id          val        rnd ----------- ---------- ---------- 1           a          b 2           b          b 3           c          b 4           d          b   I know a way using a cursor to loop throw the rows and get different random values, but that is not performant.   A clever solution to this is   select t1.id, t1.val, t2.val from #t1 t1     join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on  t1.id = t2.lfd    But I simplified the query. The real query looks more like  select *,      ( select top 1 val from t2 where t2.x &lt;&gt; t1.y order by NEWID()) rnd  from t1 order by 1;   and the simple solution doesn't fit. I'm looking for a way to force repeated evaluation of  ( select top 1 val from #t1 order by NEWID()) rnd    without the use of cursors.  Edit: Wanted output:  perhaps 1 call  id          val        rnd ----------- ---------- ---------- 1           a          c 2           b          c 3           c          b 4           d          a   and a second call  id          val        rnd ----------- ---------- ---------- 1           a          a 2           b          d 3           c          d 4           d          b   The value for each row just should be a random value independent from the other rows  Here is the cursor version of the code:  CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));  DECLARE @id INT DECLARE @val VARCHAR(10) DECLARE c CURSOR FOR SELECT id, val FROM #t1 OPEN c FETCH NEXT FROM c INTO @id, @val WHILE @@FETCH_STATUS = 0 BEGIN     INSERT INTO #res     SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd      FETCH NEXT FROM c INTO @id, @val END CLOSE c DEALLOCATE c  SELECT * FROM #res
95938
I am trying to insert all values of one table into another. But the insert statement accepts values, but i would like it to accept a select * from the initial_Table. Is this possible?
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?
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
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.
71133
I'd like to insert an input variable (integer) in a query SQL (@variable) using the algorithm postgresql execute sql in the graphical modeler of QGIS. For example, in this kind of query INSERT INTO table2 SELECT * FROM table1 WHERE ID = @variable;  where I should insert the variable in the input panel of the modeler and click play to do the query. I used pre-calculated value but with no success. Any suggestion?
13936
Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select *  from sometable order by name limit 20,10  to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically. In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this: select *  from sometable where rownum &lt;= 10 order by name  will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.
78604
I have a sql view which take only one parameter, but this parameter is a list, and a need to know how can i pass this parameter in the url  Here is my sql view  SELECT * FROM shop WHERE id IN %list%   When I put the default value for %list% as (1, 2 ,3) as example, the layer preview works and show the shopa with the default ids,  but when I try this in the url  viewparams=list:(1,2,3)   does not work.   I already tried other things like   viewparams=list:"1,2,3"   but nothing works.  Does anyone know how 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;
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?
69850
I have the following code:  Using cmd As SqlCommand = Connection.CreateCommand     cmd.CommandText = "UPDATE someTable SET Value = @Value"     cmd.CommandText &amp;= " WHERE Id = @Id"     cmd.Parameters.AddWithValue("@Id", 1234)     cmd.Parameters.AddWithValue("@Value", "myValue")     cmd.ExecuteNonQuery End Using   I wonder if there is any way to get the final SQL statment as a String, which should look like this:  UPDATE someTable SET Value = "myValue" WHERE Id = 1234   If anyone wonders why I would do this:   for logging (failed) statements for having the possibility to copy &amp; paste it to the Enterprise Manager for testing purposes
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.
26567
How can I execute a query that connects to different databases?  I have the following query, and I need to rewrite it for Drupal.  SELECT a.empid, b.empname FROM db1..empidmaster a, db2..emplist b WHERE a.empid = b.empid   How can I rewrite the query using db_query(), or db_select()?
56801
I have a function like this:  DROP FUNCTION IF EXISTS user_repo // CREATE FUNCTION user_repo(user_id INT) RETURNS VARCHAR(20) BEGIN     DECLARE vote_value mediumint;     DECLARE score mediumint;      SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)       INTO vote_value, score     FROM reputations     WHERE owner_id = user_id       AND date_time &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK));      RETURN CONCAT_WS(',', vote_value, score); END;//   It works as well. Now I want to also pass another parameter to the function to determine the time range. I mean I want to pass a string instead of  WEEK. Here is my new function:  DROP FUNCTION IF EXISTS user_repo // CREATE FUNCTION user_repo(user_id INT, range VARCHAR(10)) RETURNS VARCHAR(20) BEGIN     DECLARE vote_value mediumint;     DECLARE score mediumint;      SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)       INTO vote_value, score     FROM reputations     WHERE owner_id = user_id       AND date_time &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 1 range));      RETURN CONCAT_WS(',', vote_value, score); END;//   But it throws this error:     The following query has failed:   CREATE  DEFINER=`root`@`localhost` FUNCTION `user_repo`(`user_id` INT,                 `range` VARCHAR(10) CHARSET utf8)        RETURNS VARCHAR(20) CHARSET utf8mb4        NOT DETERMINISTIC        CONTAINS SQL SQL        SECURITY DEFINER BEGIN DECLARE vote_value mediumint; DECLARE score mediumint; SELECT  coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)     INTO  vote_value, score     FROM  reputations     WHERE  owner_id = user_id       AND  date_time &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 1 range)); RETURN CONCAT_WS(', ', vote_value, score); END      MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'range)); RETURN CONCAT_WS(',', vote_value, score); END' at line 9   Does anybody what's wrong?