prepared-statement

Can you parameterize sort order in a sql server prepared statement?

I've got a .Net web system with a VB.Net front end talking to a SQL Server 2005 back end over ADO.Net. Essentially, what I want to do is this: Dim command As SqlCommand = New SqlCommand("", connection) command.CommandText = "SELECT * FROM someTable ORDER BY orderValue @SortOrder" Dim sortParam As SqlParameter = New SqlParameter("@SortO...

How do I place an IN parameter with a LIKE with a RowSet?

I have fighting to get a IN parameter to work inside of a LIKE statement now for hours! I am using a CachedRowSet, which I understand should follow the same rules as a PreparedStatement. Here is the basic query: CachedRowSet cache; String sql = "SELECT x " + "FROM Y " + "WHERE z LIKE '?__'" cache.setComm...

When *not* to use prepared statements?

I'm re-engineering a PHP-driven web site which uses a minimal database. The original version used "pseudo-prepared-statements" (PHP functions which did quoting and parameter replacement) to prevent injection attacks and to separate database logic from page logic. It seemed natural to replace these ad-hoc functions with an object which u...

Ways to enforce prepared statements

I just stumbled (by accident) on yet another stupid not-sanitized-at-all sql injection flaw in a project I'm working on ...and I'm so tired of it. Do you have any advise on how to eliminate such bad sql statements and enforce prepared statements where ever feasible? Right now I would prefer a solution likeREVOKE DarnInlineDataStatements ...

binding results into php array

I am trying the following code to get results from query and display it in the tes.php page. db.inc.php <?php function db_connect() { $handle=new mysqli('localhost','rekandoa','rekandoa','rekandoa'); if (!$handle) { return false; } return $handle; } function get_member() { $handle=db_connect(); $sql="Select email,na...

In JDBC, why do parameter indexes for prepared statements begin at 1 instead of 0?

Everywhere else in Java, anything with an index starts at 0. Is there a reason for the change here or is this just bad design? ...

Updating a database while using a preparedStatement select

Hi I'm selecting a subset of data from a MSSql datbase, using a PreparedStatement. While iterating through the resultset, I also want to update the rows, at the moment I use something like this: prepStatement = con.prepareStatement( selectQuery, ResultSet.TYPE_FORWARD_ONLY, Resu...

fetching data from a prepared statement.

I have a function, which is to return an array of rows containg records from a database, selected based on a LIKE query. I want this query to be a prepared statement for security reasons. Apparently I can not use bound parameters and the query function as I am doing. I am unsure then, of how to keep me query as a prepared statement, and ...

Prepared Statements With MySQL in .Net @ VS. ?

According to the MySQL .Net connector docs, when running prepared statements, the @ symbol is used to defined a paramter. When trying this I got an error. Then the user comment at the bottom of the page says if you have problems, substitute the ? for the @ character. Is there any way to control what character MySQL expects for prepare...

Is it better to use a prepared Select statement when you are only doing one select?

I am currently writing a CRUD class in PHP using PDO. I like the security that prepared statements provide, but I have heard that they also prevent databases like mysql from using the queryCache. Is it better to use a prepared Select statement when you are only doing one select at a time? or would just $pdo->quote() suffice the securit...

JDBC generation of SQL in PreparedStatement

I had a really huge problem recently which took me a lot of time to debug. I have an update statement which updates 32 columns in table. I did that with PreparedStatement. Accidentaly I deleted one setParameter() call so update could not be finished successfully. I got exception from JDBC (Apache Derby) telling: "At leas one parameter ...

SQL injections with prepared statements?

If I remember correctly, I think Jeff has mentioned in the Stack Overflow podcast a possible weakness in SQL prepared statements. I'm wondering what kind(s) of weakness(es) did he refer to? Was it possibly just about inappropriate usage thereof, or something more sinister? The podcast, to my remembering, didn't go deeper into the subjec...

PreparedStatements and performance

So I keep hearing that PreparedStatements are good for performance. We have a Java application in which we use the regular 'Statement' more than we use the 'PreparedStatement'. While trying to move towards using more PreparedStatements, I am trying to get a more thorough understanding of how PreparedStatements work - on the client side ...

MySQL - pass database field through PHP function before returning result

The following code from http://php.morva.net/manual/en/mysqli-stmt.bind-result.php shows a mysqli query being prepared and executed. while ($stmt->fetch()) loop looks like it is generating the result resource. Can I change this to include a call to a function e.g. while ($stmt->fetch()) { foreach($row as $key => $val) { ...

displaying the contents of a mysql query, only first field works

I am using a mysqli prepard query with php, the code is: $retreiveQuery = 'SELECT username, firstname, lastname FROM USERS WHERE username = ?'; if ($getRecords = $con->prepare($retreiveQuery)) { $getRecords->bind_param("s", $username); $getRecords->execute(); $getRecords->bind_result($username, $firstname, $lastnam...

Why is using a mysql prepared statement more secure than using the common escape functions?

There's a comment in another question that says the following: "When it comes to database queries, always try and use prepared parameterised queries. The mysqli and PDO libraries support this. This is infinitely safer than using escaping functions such as mysql_real_escape_string." Source So, what i want to ask is: Why...

Oracle - OCI query timeout

Is there any way to set a timeout on Oracle OCI calls (OCIStmtExecute for example) ? Thanks ...

Using Prepared Statements and Connection Pooling together in MySQL

Presently, for each query, a Prepared Statement is created and reused. I am not using any connection pool. C3P0 is a widely recommended library for the same. But, as a PreparedStatement is tied to a connection. In the pooled environment, the connections are returned to the pool, effectively making PreparedStatement unusable. Am I corr...

Php PDO::bindParam data types.. how does it work?

Hi guys, im wondering what the declaration of the data type in the bind parameter (or value) is used for... I mean, i thougth that if i define a param like int, PDO::PARAM_INT, the param must be converted in int, something like $delete->bindParam(1, $kill, PDO::PARAM_INT); //should works like $delete->bindParam(1, (int)$kill); or at ...

% sign in Java's PreparedStatement

PreparedStatement ps = con.createStatement("select * from table1 where last_name like ?"); ps.setString(1, "'%"+lastName+"'"); Will this work the same as... Statement s = con.createStatement("select * from table1 where last_name like %"+ lastName); Or does PreparedStatement strip out the % sign? ...