Sometimes, it’s more convenient to save images in a database than as files. MySQL and PHP make it very easy to do this. I will describe how to save images in a MySQL database.

• Setting up the database

The difference between any regular text or integer fields and a field that needs to save an image is the amount of data that is needed to be held in the field. MySQL uses special fields to hold large amounts of data. These fields are known as blobs (Blob).

Here is the BLOB definition from the MySQL site:

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB differ only in the maximum length of the values they can hold.

For more information about MySQL Blobs check out: Reference: Blob

Use the next syntax to create a basic table that will hold the images:

CREATE TABLE Images (
PicNum int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Image BLOB
);

• Setting the upload script

What we need now is the PHP script that will get the file and insert it into the database. The next script does just that. In the script, I’m assuming that the name of the file field is “Picture”.

<?
If($Picture != "none") {
  $PSize = filesize($Picture);
  $mysqlPicture = addslashes(fread(fopen($Picture, "r"), $PSize));
 
  mysql_connect($host,$username,$password) 
                       or die("Unable to connect to server");
  @mysql_select_db($db) 
                           or die("An error occured when selecting database");
  mysql_query("INSERT INTO Images (Image) VALUES ('$mysqlPicture')") 
                   or die("Query failed");
}
else {
  echo "Failure during upload.";
}
?>

This is all that is needed to enter the image into the database. Note that in some cases you might get an error when you try to insert the image into the database. In such a case you should check the maximum packet size allowed by your MySQL version. It might be too small and you will see an error about this in the MySQL error log.

What we did in the above file is:

1. Check if a file is chosen.
2. addslashes() to the picture stream to avoide errors in MySQL.
3. Connect to MySQL, choose the database and insert the image.

• Size does matter

The theoretical limit in MySQL 4.0 is 2G, however each blob requires generally to have 3 copies of it in the memory (stored in various buffers) so you need a lot of memory, if you have large BLOBs stored in MySQL. This is the reason, why the theoretical limit can be reached only on 64bit systems. The Practical limits are around some hundreds of megs per BLOB.

While logged into MySQL, we should modify the max_allowed_packet system variable. This variable determines how large of a packet (i.e. a single row) can be sent to the MySQL server. By default, the server will only accept a maximum size of 1 meg from our client application. If you do not intend to exceed 1 meg, this should be fine. If you do intend to exceed 1 meg in your file transfers, this number has to be increased.

• Conclusion

It’s often suggested to save path to a file in database and not the file itself. Rethink everything before storing large data in your database - it can save lots of memory.

On the other hand this is extremely useful for downloading files of different types from a database, or even displaying images stored in Blob fields.

I have been playing with LINQ just a few times but I can say I am impressed with it. It does nearly everything out of box. In This post I will illustrate how to use Joins in LINQ queries.

Many a times we want a query where by we retrieve the data from one table and some the related data from the other table. Let says we have a category table and a posts table. Now when I retrieve all the records of the posts I also want to have the related category name (which is there in the category table). So I need to make a join between three tables to get the records.

Here is the LINQ Query to do the job:

var t = from p in Blog.Posts
            join cp in Blog.CategoryPosts on p.PostId equals cp.PostId
            join c in Blog.Categories on cp.CategoryID equals c.CategoryID
            select new
            {
                PostId = p.PostId,
                CategoryName = c.CategoryName,
                PostName = p.PostName,
                PostSubName = p.PostSubName           
            };

So basically to make a join between two tables we use the join keyword. After specifying the join keyword we need to provide the column name on which the join will be made. And then we need to provide the condition on which the join will be made. Hence the on keyword with the condition.

It’s so simple. If you know a little bit of SQL then this syntax shouldn’t be a problem. LINQ makes working with data in its various guises easier.

By intergating it into the language, we have rich integrated support for working with data.

However, there are times where the syntax is slighly different from what you would typically expect with TSQL. Once case where this occurs is when trying to join two data sources that are related by more than one field (also know as a composite key). This differs from standard joins where one table has a primary key and the other table has a foreign key id.

With 50,000 copies downloaded daily, MySQL has nothing to prove and is probably one of the most popular open source applications. In the acronym LAMP or WAMP, the M is MySQL (L=Linux, W=Windows, A=Apache and P=PHP). LAMP is the most popular web development system as it’s included in every Linux distro (apart from the smallest).

MySQL powers millions of websites from small to very large as well as network servers on many platforms. Following announcements from January 16^th (yesterday) MySQL has been acquired by Sun MicroSystems for the sum of $1 billion. 800 million in cash and 200 million in equility options.

Who says Open Source doesn’t make money? Of course MySQL was always dual licensed and had been selling commercial licenses and support for years. It’s how they grew to 400 employees.

I’ve been using MySQL in websites that I’ve developed mainly and also in desktop applications. Of course it’s not the only database in town; PostgreSQL and SQLite are amongst the better known open source alternatives. For me things are vague what will happen to MySQL in the future…

Sun promises to all users of MySQL that things won’t change that much - they’d even will become better (would you tell your customers anything else?). Anyway, Sun says that the same people, the same community will keep on managing MySQL. Finally Sun refers to its experience concerning Open Source products.

Everybody knows well about the issue of SQL injections.
True, but why is SQL injection still occurring? Isn’t everyone validating all user input these days?

For example say you have a logon form that accepts a user name and password. Once authenticated against the database, the application then sets a session value, or some other token for allowing the user to access the protected data.

Take a logon form for example, here you have two basic form elements, a textbox for accepting a user name, and a password box for the password.

<form action="admin.aspx"> 
<input name="username" type="textbox" /> 
<input name="password" type="password" />
<input type="submit" />

The code behind might be something like

String sQuery = "SELECT COUNT(*) FROM Users WHERE UserName = '"+username.Text+"' AND Password = '"+password.Text+"'";

SQLCommand cmd = new SQLCommand(sQuery, Connection);

int n = cmd.ExecuteScalar();

When entering “‘ or 0=0 –” into the username field the query looks like that:

SELECT COUNT(*) FROM Users WHERE UserName = '""' OR 0=0 --

which always would return the number of users.

As you can it’s just minor effort to see data of a database you are not supposed to see.

But also more bad things can happen to your database for example the following can kill lots of data.

'; drop table users --

Also the report of errorcodes can be very helpful for attackers. There’s a whole bunch of lists on the net what error code is related to which area in the server/database structure.

sQuery = "SELECT COUNT(*) FROM Users WHERE UserName = '@username' AND Password = '@password'";

SQLCommand cmd = new SqlCommand(sQuery, Connection);

cmd .Parameters.Add ("@username", SqlDbType.VarChar).Value = username.Text;

cmd .Parameters.Add ("@password", SqlDbType.VarChar).Value = password.Text;

cmd .Parameters.Add ("@return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

int n = cmd.ExecuteScalar();

This is only one way the secure your web apps - by passing parameters you avoid many types of SQL injection attacks.
Finally, ensure you provide very little information to a potential attacker when an error occurs.

You need to convert an xml file to sql the simpliest way? I got a script for you that makes out of an xml file a clear sql dump for example to insert into a mysql database.

import sys
import xml.dom.minidom as minidom
 
#field list - same in SQL + XML
fields = "name, phone, adress"
tablename = "contacts" # table
recordset = "contact" 
 
def main(args):
    f = open(args[1])
    doc = minidom.parseString(f.read())
    f.close()
    for i in doc.getElementsByTagName(recordset):
        vars = []
        vals = []
        for j in fields.split(", "):
            for k in i.getElementsByTagName(j):
                if (k and k.firstChild):
                     vars.append(j)
                     vals.append(k.firstChild.nodeValue.replace("'", "\\'"))
        sqlstring = "INSERT INTO %s (%s) VALUES ('%s');" % (
                      tablename, ", ".join(vars), "', '".join(vals))
        print sqlstring.encode('utf-8')
 
if __name__ == "__main__":
    if len(sys.argv) < 1:
        print "Usage: %s <filename>.xml"
    else:
        main(sys.argv)

xml2sql.py (right-click Save as…)

Usage is:

xml2sql.py foo.xml

Of course you first have to change the rootnode name, subnodes and fields in the script.