What is the 1033 directory or the 0409 folder?

Directories with one or more of these numbers probably litter your hard drive. Microsoft Office has them all over the place (C:\Program Files\Microsoft Office\Office10\1033). If you have installed the Plus pack, you will have a your theme files in C:\WINDOWS\Resources\1033. The .Net framework probaly installed a 1033 folder and the seach assistant has by default on my PC a C:\WINDOWS\srchasst\mui\0409. Your PC may have different numbers such as 1031 or 2057 on these directories.

For years I’ve wondered what the 1033 direcotry was. A search for that number reveals it is associated with localization settings. 1033 is a Locale ID or known more succicntly as a LCID.

So, if you look at Microsoft’s locale identification here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/882ca1eb-81b6-4a73-839d-154c6440bf70.asp

You can see that 1033 (0409 in hex) is United states English, my locale.

English has the following sub languages
us 1033
gb 2057
au 3087
ca 4105
[and many more]

There’s obviously a patter there. 1033 is fairly close to 1024. So, if we subtract out 1024, we get 9. Is 9 associated with English some how?

A Google for Language English 0x09 says it is… http://www.liquidninja.com/metapad/translations/language_ids.html

which reveals sublanguages
http://www.liquidninja.com/metapad/translations/language_ids.html#sec
0x01 SUBLANG_ENGLISH_US English (US)
0x02 SUBLANG_ENGLISH_UK English (UK)
0x03 SUBLANG_ENGLISH_AUS English (Australian)
0x04 SUBLANG_ENGLISH_CAN English (Canadian)

So how do we get 2057 for GB? Well, it’s pretty close to 2048 and the sublang is 2 so let’s try:

LCID := (1024 * SubLang) + MajorLanguage;

Yep! That did it!

So, my friend over at 404.de probably has something like:
(1024 * 1 (de-DE)) + 7 (German)

1031

Interesting. I’d figured it had something to do with localization but I wasn’t sure.

I also found a MSDN blog that confirms all of this…
http://blogs.msdn.com/oldnewthing/archive/2004/06/09/151689.aspx

Well, question answered.

SN4WP: Simple Nonces 4 WordPress

With all of the recent talk about WordPress security and Nonces, I’ve decided to create a plugin that enhances the security.

It is meant to both provide an easy way for some users to disable the referer check without giving away the house, and as a way to provide enhanced security for those that can use the referer check.

Primary Features:

  • Enables/Disables Referer check (Disabled by default)
  • Adds an optional “One IP per Admin Session” check (Disabled by default)
  • Times out sessions after inactivity (10 Minutes by default)
  • Posting – which takes longer – has a different time out period (30 Minutes by default)
  • All features are configurable on a plugin options tab

I’ll probably run my times at 5 & 30 minutes with the IP check on, but I’ll have to do more use testing first.

This is the alpha release and includes no documentation. Just extract it with paths to your plugin directory, activate it and look for the new configurations tab under the plugins directory. That said – if you didn’t know that, you shouldn’t be running the alpha release. This has been tested for one morning on one blog at this point and is my first ever WP plugin. I knew nothing about plugin writing yesterday morning. So, it is probably not ready for prime time. (That said it seems to work fine.)

Here’s the link: http://www.TheCodeCave.com/downloads/plugins/sn4wp-alpha1.zip

So please take a look and tell me what you think. Does it work?

———————-

Notes taken while making this plugin…

What does the referrer check do?
Checks to see if an admin action was initiated from an admin page.

What is protected by the referer check?
Only certain actions are protected by the referer check.
The common thread seems to be that the action must be a single destructive step.
The protected actions include: (An * indicates it was added with verison 2.02)
Categories – Delete
Link Manager – Assign, visiblity, move, add, editlink, delete
Options – Update
Plugins – Activate, Deactivate
Posts – Post*, editattachment*, editpost*, Delete, deletecomment, unapprovecomment
Profile – Post
Themes – Activate, Deactivate
User-edit – Switchposts, update*
Users – Promote, dodelete, delete adduser
Additionally the akismet plugin’s configuration page
Note that these actions consist of the the “final commit” steps. For instance the
“linkedit” action that brings up the form allowing you to edit a link is not protected.
However, the “editlink” action that posts the changes IS protected. Please keep this in
mind when testing this plugin.

Can a referrer be wrong?
Yes, many proxies strip or replace the referer. Additionally referers can be forged,
but the fact that a login cookie is also required makes such attacks difficult. The
referer check, when working CAN protect you from some attacks. That’s why this plug
in does not disable it by default.

Can a referer check be simulated in a plugin? And if so how?
While we cannot check to see if each individual action came from an admin page, we
can ensure that an admin page was recently used by that user. If that user just
accessed an admin page, allow the action to succeed.

Problem: All action links change pages before taking effect. So the page last viewed
by the user by the time the admin referer check hits is ALWAYS an admin page.
Additionaly, the final post action involves multiple pages which would violates any
LastPage tracking scheme used in Nonces (even after you implement it).
Solution: Without access to the name of the action that is about to be attempted, the
nonce needs only indicate the previous page was an admin page. This can be
indicated by whether or not the Nonce is valid.

Problem: An admin could close their admin page without logging out.
Solution: The nonce times out after 5 minutes. They are vulnerable for 5 minutes if
they forget to log out.

Problem: An admin could leave the admin area and go back to their blog without
logging out.
Solution: None – Admins often use one tab to admin their blog, and another to view it.
Likewise, the preview has a non admin context.

Cross Site Request Forgery

In June of 2001, Peter Watkins defined the term Cross Site Request Forgery – pronounced Sea Surf. He keeps that discussion here: http://www.tux.org/~peterw/csrf.txt

I’d posted a copy of this text localy on my site and now I’ve now found I have a number of people linking to it.

So, I thought I’d turn it into an object lesson demonstration.

If you got to this link by clicking on a link to http://www.TheCodeCave.com/csrf.txt, you may be surprised to noticed that you are not looking a text file. That’s because I’ve intercepted your request and sent it to another location on my site. This is what a CSRF attack does however it bounces the attack back at you. I could have just as easily detected if you were an admin in any of the most popular open source projects out there, sent you to your site with an attack tailored to your software and then without taking a breath, put you into the text file again. Kinda scary isn’t it?

The trick is to address the danger by making sure that all of your web pages are secure. I’ve been planning for a long time to write a series of post describing what I’ve learned about PHP security. I just haven’t figured out a way to do it without creating a tutorial site. If you check back here: http://www.thecodecave.com/?cat=7 periodically, you can see what I’ve come up with.

From: Peter W <peterw@usa.net>
To: John Percival <john@jelsoft.com>
Cc: bugtraq@securityfocus.com, clambert@whitecrown.net, peterw@
tux.org
Subject: Cross-Site Request Forgeries (Re: The Dangers of Allowing 
Users to Post Images)
Message-ID: <20010615011542.C22677@usa.net>
References: <04f901c0f437$4911b610$9701a8c0@wellingtoncollege.
berks.sch.uk>
In-Reply-To: <04f901c0f437$4911b610$9701a8c0@wellingtoncollege.
berks.sch.uk>; from john@jelsoft.com on Wed, Jun 13, 2001 at 07:33:
04PM +0100

	Cross-Site Request Forgeries
		(CSRF, pronounced "sea surf")

I hope you don't mind if I expand on this a bit. You've come across 
the tip, in my opinion, of a rather large iceberg. It's another
Web/trust-relationship problem. Many Web applications are fairly 
good at identifying users and understanding requests, but terrible 
at verifying origins and intent.

The problem isn't the IMG tag on the message board, it's the 
backend app you seek to attack via the IMG tag. And I suspect lots 
of Web apps are vulnerable. Lots. I've been to training on highly-
regarded, widely-used, expensive Web app development frameworks, 
and none of the classes taught how to avoid the problems I will 
attempt to describe. In fact, they all seem to teach the "easy way" 
of handling what look like user requests, which is, of course, the 
vulnerable way. 

Anyway, let's look at how your post relates to what I call CSRF.

On Wed, Jun 13, 2001 at 07:33:04PM +0100, John Percival wrote:

> This exploit shows how almost any script that uses cookie 
> session/login data to validate CGI forms can be exploited if the 
> users can post images.  What is the problem? Well, by using an 
> [img] (or HTML <img> or <iframe> or <script src="">) tag, 
> the user is having anyone who views the thread access that image - 
> that is perform an HTTP GET on the URL specified for the image.
> Even if its not an image, it still can be accessed, but will 
> display a broken image. 

Depending on what's allowed, height/width and CSS/visibility tags 
can be used to hide the broken image icon.

> This means that the user can put a CGI script inside [img]
> tags.

** Learning from Randal's purple dinosaur?

The problem you describe is not uploading images, it's allowing 
users to post code that's inserted in an appropriate HTML tag 
attribute. This is something of a variation on Randal Schwartz's 
purple dinosaur hack,[2] but much more interesting and dangerous 
than even what you describe.

> This script will be called by whoever views that thread. 
> When used maliciously, it could force the user to: unknowingly 
> update their profile, respond to polls in a certain way, post new 
> messages or threads, email a user with whatever text they want, 
> the list goes on. This would be particularly worrying for a 'worm' 
> to spread through a forum, filling it with rubbish posts.

** The difference between XSS and CSRF

Right. There's something much larger going on here. Darnit, I 
wanted to make a nice formal paper out of this, but you're forcing 
my hand. :-) The problem is what I call CSRF (Cross-Site Request 
Forgeries, pronounced "sea surf"). Any time you can get a user to 
open an HTML document, you can use things like IMG tags to forge 
requests, with that user's credentials, to any Web site you want -- 
the one the HTML document is on, or any other.

This looks somewhat similar to Cross-Site Scripting (XSS), but is 
not the same. XSS aimed at inserting active code in an HTML 
document to either abuse client-side active scripting holes, or to 
send privileged information (e.g., authentication/session cookies) 
to a previously unknown evil collection site. 

CSRF does not in any way rely on client-side active scripting, and 
its aim is to take unwanted, unapproved actions on a site where 
the victim has some prior relationship and authority.

Where XSS sought to steal your online trading cookies so an attacker 
could manipulate your portfolio, CSRF seeks to use your cookies to 
force you to execute a trade without your knowledge or consent (or, 
in many cases, the attacker's knowledge, for that matter). [Just an 
extreme example there; I do not have any idea if any trading sites 
are vulnerable. I have not tested *any* applications or sites that I 
don't have some personal involvement in the design and maintenance 
of. Don't ask me to.]

<img src="https://trading.example.com/xfer?from=MSFT&to=RHAT
&confirm=Y">
<img src="https://books.example.com/clickbuy?book=ISBNhere
&quantity=100">

** Ubiquity of attack channels

Since HTML documents are popping up everywhere (even in 
corporate email systems!!!), and it's impossible to discern what IMG
or HREF values might be direct CSRF attacks, or redirect users to 
unwittingly do dangerous things via CSRF redirects, the fix has to 
be in the applications that do the interesting things.

> For example, if a user posted something along these lines:
> [img]http://your.forums/forums/newreply.cgi?action=newthread&
> subject=aaa&body=some+naughty+words&submit=go[/img]
> Then the post would go through, under the name of whoever 
> viewed the image.
> This is of particular danger when an administrator views an image, 
> which then calls a page in an online control panel - thus granting 
> the user access to the control panel.

** Impossible to filter content

Right, and as I say, the site you act against can be somewhere else 
entirely. Here's what a CSRF attack might look like:
 <img src="http://example.net/logo.gif" height=0 width=0 alt="">
That's it. When your client requests logo.gif - exposing no cookies 
- the example.net server redirects you to a URL like the one you 
show, above. So the end result us the same as if the attacker had 
embedded the more obvious URL inside the IMG tag. 

If an attacker wants, he can also use a simple, innocent looking 
hyperlink and hope the victim clicks on it (http://example.net/
kyotoanalysis.htm). You don't allow hyperlinks? Well, someone might 
copy/paste the link, and be stung that way. They'd notice? Maybe 
not -- the URL could be a mostly useful page, with a tiny frameset 
sliver that loads your attack URL.

> How can it be fixed? Well, there are a couple of ways to stop it, 
> but the easiest (in PHP at least) seems to be to have most of the 
> variables used by scripts be used through $HTTP_POST_VARS. So 
> instead of checking for $action in a script, $HTTP_POST_VARS
> ['action'] would be checked. This forces the user to use a POST 
> request, not a GET. 

which means the attacker reverts to using Javascript, or entices 
the victim to click on an image that's acting as a submit control 
in a <form>.  Requiring POST raises the bar, but doesn't really 
fix the problem.

> Alternatively, the sessionid could be required to come with the 
> GET/POST request variables, rather than by cookie.

...thereby exposing an important piece of authentication 
information to history files and proxy servers; I really don't like 
URL mangling for authentication purposes, especially in non-SSL 
systems. A combination of cookie + URL mangling might not be bad, 
though in the message board case, a CSRF attacker could use an 
intermediate redirect (as described earlier) to get the URL 
mangling (from the Referer), and redirect back to the messageboard 
with the proper mangling as well as all cookies that might be 
expected/needed. So in your example case, URL mangling would buy 
nothing. :-(

> Finally, in the specific case of [img] tags, the use of ? or & in 
> the img URL can be disabled by some regexes.

Not at all adequate. Browsers follow redirects on IMG tags, so I 
redirect you to http://example.net/logo.gif which in turn redirects 
you to the final URL, as described earlier.

> If the software that you run is not secure, we recommend that 
> you disable HTML and/or [img] tags, until the fixes have been 
> implemented.

It's much worse than that.

Please see the following URLs for an introduction to the dangers 
of CSRF, and some discussion of countermeasure strategies. 

 http://www.astray.com/pipermail/acmemail/2001-June/000803.html
 http://www.astray.com/pipermail/acmemail/2001-June/000808.html
 http://www.astray.com/pipermail/acmemail/2001-June/000804.html

** Server-Side Countermeasures

The fix MUST be implemented on the backend that's being attacked. 
In your example, newreply.cgi needs to be intelligent enough to 
detect and stop CSRF attacks. 

We've talked about how an attacker can post a message to the 
messageboard with innocent looking URLs. But an attacker can also 
simply send the victim a piece of HTML email including the full 
attack IMG URL. No amount of IMG tag filtering in your 
messageboard posting system can stop that.

** Three-phase tests before acting

When it comes to generic CSRF attacks, any application that 
uses a two-phase approach to action approval is vulnerable (the 
two phases being [1] do you possess authentication information 
and [2] are all the required arguments present). What's needed is 
a third test: is the user really using a proper application form to 
generate the request?

** The 90% solution: Referer tests

For many sites, you can achieve a high level of protection by 
checking the HTTP Referer header. This would prevent things like 
attacks via email. But it would also mean locking out any user whose 
requests did not contain Referer information.[1] As long as the 
values in the allowed Referer list are all coded with XSS and CSRF 
in mind, this could be adequate.  Referer checks should be as 
specific as possible, e.g. you might require the Referer to begin 
with: 
"https://example.com/admin/admin.cgi" or "https://example.com/
admin/" instead of simply "https://example.com/".

** The more difficult cases

Some other applications are more difficult to secure. Consider 
webmail apps. So webmail.example.com decides only "message 
delete" requests from webmail.example.com pages will be accepted: 
well, if the attacker sends a CSRF message to your webmail account, 
then when you read it via webmail, the Referer in the CSRF image 
request (your client thinks it's an image request) says it's indeed 
from the proper webmail server (even in the case of an intermediate 
redirect; check the bugtraq archives for past discussion of 
anonymizing hyperlinks, redirects vs. client-pull, etc.), so the 
request gets through. Basically, any application that allows posting 
of URLs needs more sophisticated protection than Referer checks. 
This would also include messageboards and discussion sites like 
Slashdot. 

> Known Vulnerable: Infopop's UBB 6.04e (probably the whole 6.xx 
> series), ezboard 6.2, WWW Threads PHP 5.4, vBulletin 2.0.0 
> Release Candidate 2 and before (later versions are safe). Probably 
> many more bulletin boards and CGI scripts out there, but those are 
> the main ones that we have been tested positive.

** One-time authorization codes

The URLs I list above outline a server-side one-use-token approach 
to closing the hole. For instance, the page that users are expected 
to use for drafting messages (in your newreply.cgi example) would 
create a one-time use token, good for a limited time. The newreply.
cgi processing script would require this value be present, correct, 
and in time. So while the attacker knows that action, subject, body, 
and authcode values are required, the attacker does not know, and 
cannot ascertain, the proper value needed for the authcode 
argument.[0] These tactics tend to introduce certain 
inconveniences (e.g., preventing use of the "back" button) so you  
may wish to analyze the various actions your application can take 
and provide varying levels of protection. For example, in a webmail 
system sending and deleting messages need more protection than 
displaying messages.

** Unpredicatable argument names?

Other tactics may be possible. For instance, consider
"action=newthread&subject=aaa&body=some+naughty+words&
submit=go". On the server side, you could have an "argument map 
table" for each session, e.g. pick random surrogates for the normal 
argument names. For one user, the system might look for 
"876575665" as an argument name instead of the predictable 
"action", "9876dafd987" for "body", etc. There may be some 
tricks vis-a-vis anonymizing referers if the labels are constant 
throughout a session, but it might be possible to do something
like this to make it more difficult to construct a valid URL for a 
CSRF malicious action.

** Attacking sites behind corporate firewalls

Want more fun? CSRF tactics can be used to attack servers 
behind corporate firewalls. It's not just your public Web apps that 
are at risk. 
<img src="http://intranet/admin/purgedatabase?rowslike=%2A&
confirm=yes">
If the attacker knows enough to make a URL and can get you to 
open a message, that's all it takes. Here we see that HTTP 
Referer headers can be a double-edged sword. Earlier we 
described how Referer tests can add security to many apps 
relatively easily. But Referer headers can also leak information 
about "private" sites if those sites use non-anonymized hyperlinks 
and external document references.

I'm afraid CSRF is going to be a mess to deal with in many cases. 
Like trying to tame the seas.

** Workarounds

Most of us probably depend on applications that won't be fixed
anytime soon. So what can you do to prevent a CSRF attack 
from making your browser request something without your 
approval?
 - Do not use an email client that renders HTML
 - Do not use a newsgroup client tied to your Web browser
 - Do not allow your browser to save usernames/passwords
 - Do not ask Web sites you care about to "remember" your login
 - Be sure to "log off" before and after using any authenticated
   Web site that's important to you [or your employer ;-)], 
   even if that means exiting your Web browser completely
 - Consider using something like Windows 2000's "Run As" 
   shortcut feature or my "runxas" shell script (available at the 
   tux.org URL listed below) to run a Web browser for casual 
   use.

My apologies for the somewhat rambling nature of this post; I may 
yet clean this up and put it in a proper paper, and do some real 
editing... but I hope even in this rough form it makes some sense, 
and helps folks design better, safer applications.

-Peter
http://www.tux.org/~peterw/

[0] Not unless the page that included the authcode is readable, 
e.g. if the composition page had XSS bugs that would facilitate 
construction of a URL for a CSRF attack.

[1] As discussed earlier (http://www.securityfocus.com/arch
ive/1/41653), client-pull pages usually result in no Referer 
information being sent by the client. So if your application 
allows a request with no Referer, an attacker need only direct 
the victim to an HTML document that uses a client-pull META 
tag to send the victim to the CSRF attack URL. This might be 
tricker to pull off, but remains feasible. So if you want to use 
Referer checks, you really ought to go all the way and deny 
every request that lacks a Referer header.

[2] http://www.stonehenge.com/merlyn/ [3]

[3] fellow cornfed users: the horror! footnotes referenced in 
reverse order!

I entered a WP ticket the other day…

We’ll see where it goes…

http://trac.wordpress.org/ticket/2666

Ticket #2666
WordPress shouldn’t use URI instead of URL just because URI is geeky cool.

——————————————————————————–
Priority: normal Reporter: SilverPaladin
Severity: minor Assigned to: anonymous
Component: General Status: new
Version: 2.1 Resolution:
Milestone: Keywords: URL URI Documentation website bg|needs-patch bg|2nd-opinion

——————————————————————————–

Description by SilverPaladin:
I know this is a topic that comes up periodically, but the last time I found it specifically addressed on the WordPress Forum was 2004 and no one directly addressed it even then. So please bear with me if you think you’ve heard it all before, for I’m going to try to be to the point.

I should say, Yes, I do know the difference between the a URI and a URL and did the obligatory URL vs URI blog entry to prove it. The link is here http://www.TheCodeCave.com/?p=93, but if you know the difference between a url and URI you don’t need to visit. This isn’t a “get visitors” stunt, but I knew people might try to explain the details to me, and I wanted to outline my thoughts on the subject and definitions before a Q&A session was started.

Now, to the meat of it all… I am speaking against the use of URI specifically in the Options form, but also in the documentation. WordPress is asking for a URI when it will not accept all valid URI. Therefore it is asking for the wrong thing.

The simple fact that WordPress it specifies *address* in the prompts for the “WordPress address (URI):” and “Blog address (URI):” is a clue that it only wants an URL.

Beyond that blogs now can have URNs (which are valid URIs). My blog, like many others, has an ASIN from Alexa. Therefore, a valid formated URI for my blog would be: ASIN:B000F1J35C. That fits the URN specification and it for now an for ever more will uniquely identify my site even after my site has long disappeared from the web. However, can I enter that into the URI field in WordPress? No, of course not. You CANNOT enter a URN into those fields, because WordPress does not want any old URI. WordPress specificially wants an URL.

My main point here, is that it is foolish to use a fancy term that new users don’t know just so that your software looks technical and geeky. This is the sort of slippery slope that you have with open source projects. Bad habits are promoted when smaller projects use a bad term or code segment that is picked up in larger projects.

WordPress should do the right thing and change the term back from URI to URL.

I see four primary reasons to do this: * URL is the language used in most professional projects. * The term URI is not known to the general computing public. * URL is more accurate in all WordPress use cases. * Some valid URIs would produce errors if entered into the URI field.

Can anyone provide any reasonings for using the less specific URI term other than “It’s the current fad.”? If not, I’d support a decision that the next major release include documentation and code changes required to replace URI with URL.

ALWAYS ALWAYS ALWAYS TRIPLE CHECK the TO address…

Well, if you’re visiting my site after my major blunder in the discussion about WP security, please feel free to leave a comment!

(No Images Please! 🙂 )

Sigh, yes during a public discussion of Word press security on a public email list, I discovered some minor holes that could be exploited under certain conditions.

So, I emailed them to very exclusive Security@Wordpress.com. This morning I wanted to provide all of the details in an update to them. So I cut and pasted the details of the attacks into an email. The result was basically a one stop shop of how to attack a website – creating admin users and stealing cookies. I made sure I had no mistakes in it and sent it off. However, I grabbed the wrong email address.

I sent it to the public mass mail list.

Sigh…. I’d intended this site to discuss security issues. Just not so openly…

So, Welcome!

If the public record can’t be cleared off of the mailing list archive, I guess we move on to discussing the best way to protect yourself.

MySQL Commands Cheat Sheet

I wanted to get a list of the tables in my MySQL database a couple weeks ago. I bookmarked on place that had a list of commands. I’m including that info here for furture reference. I’ve found dozens of copies of that list elsewhere, so I’m not gonna link to any particular site.

Check back here periodically as I add more and more commands here. There are already 5 good ones that I need to put on later tonight…but I’ve got a hot date with my wife that takes priority!

Common MySQL Commands

Description Command
Administrative
To login as root (from shell) mysql -uroot -p[password]
Create a database create database [db name];
Grant all permissions for a database to a user grant all privileges on [db name].* to ‘[user]’@’localhost’ identified by ‘[user password]’;
List all databases on the sql server. show databases;
Switch to a database. use [db name];
To see all the tables in the db. show tables;
To see database’s field formats. describe [table name];
To delete a database drop database [db name];
Dump all databases for backup.Backup file is sql commands to recreate all db’s. mysqldump --user=root --password=blah --all-databases

>/tmp/sql-01_backup.sql

Exit mysql command line quit
Queries (SELECTS)
Show all data in a table. SELECT * FROM [table name];
Count rows. SELECT COUNT(*) FROM [table name];
Show certain selected rows with the value “whatever”. SELECT * FROM [table name] WHERE [field name] = “whatever”;
Show all records containing the name “Bob” AND the phone number ‘3444444’. SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ‘3444444’;
Show all records not containing the name “Bob” AND the phone number ‘3444444’ order by the

phone_number field.

SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ‘3444444’ order by

phone_number;

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’. SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’;
Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This

finds any record beginning with a.

SELECT * FROM [table name] WHERE rec RLIKE “^a$”;
Show unique records. SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc). SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Join tables on common columns. select lookup.illustrationid, lookup.personid,person.birthday from lookup
left join person on lookup.personid=person.personid=statement to join birthday in person table with primary

illustration id;

User Management
Switch to the mysql db. Create a new user. INSERT INTO [table name] (Host,User,Password) VALUES(‘%’,’user’,PASSWORD(‘password’));
Change a users password.(from unix shell). [mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password

‘new-password’

Change a users password.(from MySQL prompt). SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
Switch to mysql db.Give user privilages for a db. INSERT INTO [table name]

(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES

(‘%’,’db’,’user’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);

Update database permissions/privilages. FLUSH PRIVILEGES;
Table Alteration
To delete a table. drop table [table name];
Returns the columns and column information pertaining to the designated table. show columns from [table name];
To update info already in a table. UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where

[field name] = ‘user’;

Delete a row(s) from a table. DELETE from [table name] where [field name] = ‘whatever’;
Delete a column. alter table [table name] drop column [column name];
Add a new column to db. alter table [table name] add column [new column name] varchar (20);
Change column name. alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes. alter table [table name] add unique ([column name]);
Make a column bigger. alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table. alter table [table name] drop index [colmn name];
Load a CSV file into a table. LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES

TERMINATED BY ‘\n’ (field1,field2,field3);

Create Table
Example 1.
CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname

VARCHAR(35),suffix VARCHAR(3),
officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table
Example 2.
create table [table name] (personid int(50) not null auto_increment primary key,firstname

varchar(35),middlename varchar(50),lastname varchar(50) default ‘bato’);