sql

Flat File Databases in PHP

What are your best practices around creating flat file database structures in PHP? A lot of the more mature PHP flat file frameworks I see out there attempt to implement SQL-like query syntax, which is over the top for my purposes in most cases (I would just use a database at that point). Are there any elegant tricks out there to get g...

Decoding T-SQL CAST in C#/VB.net

Recently our site has been deluged with the resurgence of the ASPRox bot SQL Injection attack. Without going into details, the attack attempts to execute SQL code by encoding the T-SQL commands in an ASCII encoded BINARY string. It looks something like this: DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x44004500...06F007200%20AS%20NVARC...

ASP.NET Site Maps

Has anyone got experience creating SQL-based ASP.NET site-map providers? I've got the default XML web.sitemap file working properly with my Menu and SiteMapPath controls. But I'll need a way for the users of my site to create and modify pages dynamically. I'll need to tie page viewing permissions into the standard ASP.NET membership s...

How to export data from SQL Server 2005 to MySQL

I've been banging my head against SQL Server 2005 trying to get a lot of data out. I've been given a database with nearly 300 tables in it and I need to turn this into a MySQL database. My first call was to use bcp but unfortunately it doesn't produce valid CSV - strings aren't encapsulated so you can't deal with any row that has a str...

Is there a version control system for database structure changes?

I often run into the following problem. I work on some changes to a project that require new tables or columns in the database. I make the database modifications and continue my work. Usually, I remember to write down the changes so that they can be replicated on the live system. However, I don't always remember what I've changed and I ...

cx_Oracle - How do I access Oracle from Python?

How do I get started? ...

cx_Oracle - what is the best way to iterate over a result set?

There are several ways, what is the best one? ...

Swap unique indexed column values in database.

I have a database table and one of the fields (not primary key) is having unique index on it. Now I want to swap values under this column for two rows. How could this be done? Two hack I know are: Delete both rows and re-insert them Update rows with some other value and swap and then update to actual value. But I don't want to go for...

How do I index a database field

Hopefully, I can get answers for each database server. For an outline of how indexing works check out: http://stackoverflow.com/questions/1108/how-does-database-indexing-work...

What is the best way to handle multiple permission types?

I often encounter the following scenario where I need to offer many different types of permissions. I primarily use ASP.NET / VB.NET with SQL Server 2000. Scenario I want to offer a dynamic permission system that can work on different parameters. Let's say that I want to give either a department or just a specific person access to an a...

What language do you use for Postgresql triggers and stored procedures?

Postgresql is interesting in that it supports several languages for writing stored procedures. Which one do you use, and why?...

What is the best way to avoid SQL injection attacks?

I've provided a solution for Python... please flesh this out with examples for other languages. ...

Best Book for a new Database Developer

We have just had a graduate join the team with the end aim of assisting out our very busy DBA. He has only a basic SQL knowledge from his degree so we are looking for a really good getting started book preferably based on MS SQL server. Purchase Update: Thanks to the replies we have now purchased Head First SQL to review what he alr...

Convert HashBytes to VarChar

I want to get the MD5 Hash of a string value in SQL Server 2005, I do this with the following command: SELECT HashBytes('MD5', 'HelloWorld') However, this returns a VarBinary instead of a VarChar value. If I attempt to convert "0x68E109F0F40CA72A15E05CC22786F8E6" into a VarChar I get "há ðô§*à\Â'†øæ" instead of "68E109F0F40CA72A15E05CC...

ASP.NET MVC "CRUD" Database Sample

I'm trying to get my head around ASP.NET MVC coming from a LAMP development environment. This isn't for anything production or mission-critical, just a guy trying to learn. I've looked at all I can on http://asp.net/mvc but a lot of those videos and tutorials seem to assume you know ASP.NET WebForms (which I don't) although I am quite ha...

Datatable vs Dataset

I current use a datatable to get results from a database which I can use in my code. However, many example on the web show using a dataset instead and accessing the tables through the collections method. Is there any advantage, performance wise or otherwise, of using datasets or datatables as a storage method for sql results?...

Split String in SQL

Using SQL Server 2005, how do I split a string so I can access item x. For example, take the string "Hello John Smith". How can I split the string by a space and access item 1 which should return "John"?...

What all do I need to escape when sending a (My)SQL query?

When you execute a SQL query, you have to clean your 'strings' or users can execute malicious SQL on your website. I usually just have a function escape_string(blah), which: Replaces escapes () with double escapes (\). Replaces single quotes (') with an escaped single quote (\'). Is this adequate? Is there a hole in my code? Speedy...

How do I use T-SQL Group By

I know I need to have (although I don't know why) an Order By clause on the end of a SQL query that uses any aggregate functions like count, sum, avg, etc: select count(userID), userName from users group by userName When else would GROUP BY be useful, and what are the performance ramifications?...

SQL 2005 For XML Explicit - Need help formatting

I have a table with a structure like the following: ------------------------------ LocationID | AccountNumber ------------------------------ long-guid-here | 12345 long-guid-here | 54321 To pass into another stored procedure, I need the XML to look like this: <root><clientID>12345</clientID><clientID>54321</clientID></root> The...