Popular in Course
Popular in ComputerScienence
This 42 page Class Notes was uploaded by Herbert Murphy on Tuesday October 20, 2015. The Class Notes belongs to CS580 at San Diego State University taught by Staff in Fall. Since its upload, it has received 33 views. For similar materials see /class/225306/cs580-san-diego-state-university in ComputerScienence at San Diego State University.
Reviews for CLIENT
Report this Material
What is Karma?
Karma is the currency of StudySoup.
Date Created: 10/20/15
11600 D0019 Some MySQL slide 1 CS 580 ClientServer Programming Fall Semester 2000 Doc 19 Some MySQL Contents SQL 2 History 2 MySQL 3 Database amp tables 3 MySQL Names 4 MySQL Data Types 6 MySQL Columns Types 7 Numeric 7 String Column Types 8 Date amp Time Column Types 9 Basic SQL Commands 1O Indexing 11 Operators 15 References MySQL Paul DuBois New Riders Publishing 2000 This is a very good book A number of examples and tables in this lecture are from this text OnIine MySQL Manual at httpwwwmvsqlcomdocumentationindexhtml 11600 D0019 Some MySQL slide 2 SQL History Structured query language SQL Dr E F Codd develops relational database model Early 197039s IBM System R relational database Mid 197039s Contained the original SQL language First commercial database Oracle 1979 SQL was aimed at Accountants Business people SQL89 Not well followed ANSI X31351989 SQL92 First commonly followed standard ANSI X31351992 SQL2 ISOIEC 90751 through 5 New SQL standard 11600 D0019 Some MySQL slide 3 MySQL Database amp tables Database consists of a number of tables Table is a collection of records Each Column of data has a type l firstname l lastname l phone l code l John l Smith l 555 9876 l 2000 l Ben l Oker l 555 1212 l 9500 Mary Jones 555 3412 l 9900 11600 D0019 Some MySQL slide 4 MySQL Names Databases tables columns amp indexes have names Legal Characters Alphanumeric characters g Names can start with digits Name length Up to 64 characters tables databases columns amp indexes Name qualifiers A table is in a database Full name of a table is databaseNametableName A column is in a table Full name of a table is databaseNametableNamecolumnName Often the full name is not needed 11600 D0019 Some MySQL slide 5 Example of Nonqualified Names Set a default database USE acm now select some columns SELECT astname firstname FROM members acm is a database members is a table in the acm database astname amp firstname are columns in members Case Sensitivity SQL keywords and function names Not case sensitive Database amp table names Are implemented using directories and files Case sensitivity depend on OS Column and index names Not case sensitive 11600 D0019 Some MySQL slide 6 MySQL Data Types Numeric Values Integer decimal or hex Floatingpoint scientific amp 121234 String Values Use single or double quotes quotthis is a stringquot 39So is this39 Sequence Meaning 0 NUL ASCII O 39 Single quote quot Double quote b Backspace n Newline r Tab Backslash Including a quote character in a string Double quote the character 39Donquott do it39 quotHe said quotquotGo homequotquot quot Use the other quote character quotDon39t do itquot 39He said quotGo home Escape the quote character with a backslash Date and Time NULL 11600 D0019 Some MySQL slide 7 MySQL Columns Types Numeric Type Range TlNYlNTM Signed Values 128 to 127 Unsigned Values 0 to 225 SMALLINTM Signed Values 32768 to 32767 Unsigned Values 0 to 65535 MEDUIMINTM Signed Values 8388608 to 8388607 Unsigned Values 0 to 16777215 NTM Signed Values 2147683648 to 2147683647 Unsigned Values 0 to 4294967259 BIGINTM Signed Values 9223372036854775808 to 9223372036854775807 Unsigned Values 0 to 2321 FLOATMD MIN VALUES l1175494351E38 FLOAT4 MAX VALUES t340282346638 DOUBLEMD MIN VALUES I222507E308 FLOAT8 MAX VALUES l179769308 DECIMALMD Depends on M amp D lnts amp Floats M number of digits to the left of the decimal displayed D number of decimal places displayed M amp D do not affect how the number is stored DECIMAL Stored as a string M amp D determine how many characters are stored 11600 D0019 Some MySQL slide 8 String Column Types Type Max Size CHARM M lt225 bytes VARCHARM M lt225 bytes 39HNYBLOB zitbwes 39HNYTEXT BLOBTEXT 2mlbwes MEDIUMBLOB 2241 bytes MEDMNWEXT LONGBLOB 2W4twws LONGTEXT ENUMquotvalue1quot 65535 members SETquotvalue1quot 64 members CHAR amp VARCHAR are the most common string types CHAR is fixedWidth VARCHAR BLOBs and TEXTs are variable width Fixedlength row row containing just fixed length items Processed much faster than variablelength rows MySQL generally converts CHARs to VARCHARS in tables with variablelength rows BLOB Binary Large OBject amp Text BLOBs use case sensitive comparisons TEXT uses case insensitive comparisons 11600 D0019 Some MySQL slide 9 Date amp Time Column Types Type Range DATE quot10000101quot to quot99991231quot TIME quot8355959quot t0 quot8385959quot DATETIME quot10000101 000000quot to quot99991231 235959quot TMESTAMPM 19700101000000 to year 2037 YEARM 1901 to 2155 DATE is time of day TIME is elapsed time quot1230quot represents quot001230quot 11600 D0019 Some MySQL slide 10 Basic SQL Commands CREATE TABLE tablename colname coltype NOT NULL PRHVIARY KEY colname coltype NOT NULL PRIMARY KEY DROP TABLE tablename INSERT INTO tablename column column VALUES value value DELETE FROM tablename WHERE column OPERATOR value AND OR column OPERATOR value SELECT tablecolumn tablecolumn FROM table alias table alias WHERE tablecolumn OPERATOR VALUE AND OR tablecolumn OPERATOR VALUE ORDER BY tablecolumn DESC tablecolumn DESC UPDATE tablename SET columnvalue columnvalue WHERE column OPERATOR value AND OR column OPERATOR valuequotlt OPERATOR can be ltgtltgtltgt or LIKE VALUE can be a literal value or a column name 11600 D0019 Some MySQL slide 11 Indexing Column indexes make queries more efficient MySQL before 3232 did not allow indexed columns to be 0 NULL 0 BLOB o TEXT Unique amp Primary Columns Unique index with out duplicate values Primary key unique column with index name Primary 11600 Can use 0 ALTER TABLE 0 CREATE INDEX 0 CREATE TABLE D0019 Some MySQL slide 12 Creating Indexes 11600 D0019 Some MySQL slide 13 Examples CREATE Form at CREATE TABLE tab1ename create columns then declare indexes INDEX indeXname column1ist UNIQUE indeXname column1ist PRIMARY KEY column1ist more stuff CREATE TABLE roger sam INT NOT NULL PRIMARY KEY SAM CREATE TABLE roger sam INT NOT NULL PRHVIARY KEY CREATE TABLE students name CHAR25 address CHAR60 INDEX name address 11600 D0019 Some MySQL slide 14 Alter Table ALTER TABLE tablename ADD INDEX indexname column1ist ALTER TABLE tab1ename ADD UNIQUE indexname column1ist ALTER TABLE tablename ADD PRHVIARY KEY column1ist Create Index CREATE UNIQUE INDEX indexname ON tablename column1ist CREATE INDEX indexname ON tablename column1ist 11600 D0019 Some MySQL slide 15 Operators Arithmetic 1 7 7 7 Logical AND ampamp OR NOT Bit Operators amp ltlt 3 ltlt b left shift of a by b bits gtgt right shift 11600 D0019 Some MySQL slide 16 Comparison Operators Operator Example ltgt lt lt gt gt IN aINxyz BETWEEN a BETWEEN b AND C LIKE a LIKE b NOT LIKE REGEXP RLIKE a REGEXP b NOT REGEXP ltgt a ltgt b equal even if NULL IS NULL a IS NULL IS NOT NULL Binary strings CHAR BINARY VARCHAR BINARY and BLOB types Binary string comparisons are case sensitive Nonbinary string comparisons are not case sensitive 11600 D0019 Some MySQL slide 17 BINARY operator MySQL 323 Convert a string to binary BINARY quotabcquot quotAbcquot Like amp Regexp LIKE patterns match only if the entire string is matched REGEXP patterns match if the pattern is found anywhere in the string LIKE is not case sensitive unless at least one operand is a binary string REGEXP starting in 3234 uses LIKE39s case sensitive rules 11600 D0019 Some MySQL slide 18 Like Pattern Matching Character Meaning matches any single character matches 0 or more characters of any value escapes special characters All other characters match themselves 11600 D0019 Some MySQL slide 19 Regexp Pattern Matching Sequence Meaning quot Match the beginning of the string Match the end of string period Match any single character Match any character between the brackets quot Match any character not between the brackets E Match zero or more instance of pattern E E Match one or more instance of pattern E E Match zero or one instance of pattern E E1 E2 Match E1 or E2 Em Match m instances of E En Match zero to n instances of E Em Match m or more instances of E Emn Match m to n instances of E Group elements in to one element All other characters match themselves Expression Result quotabcquot REGEXP quotaCquot quotabcquot REGEXP quotazquot quotabcquot REGEXP quotquotazquot quotabcquot REGEXP quotquotabcquot quotade REGEXP quotquotabcquot quotabcquot REGEXP quotabc2quot quotabcabcquot REGEXP quotabc2quot 12502 Doc 25 Security slide 1 CS 580 ClientServer Programming Fall Semester 2002 Doc 25 Security Contents Security 2 General Security Issues 8 OneWay Hash Functions 11 Encryption 17 References NIH Security Web Site httpwwwalwnihqovSecuritvsecuritvhtml Applied Cryptography Second Edition Bruce Schneier John Wiley amp Sons 1996 Red Team versus the Agents Scientific American December 2000pp2024 12502 Doc 25 Security slide 2 Security Some common issues Authentication Client server should be able to ascertain the origin of the message Intruder should not be able to masquerade as someone else Integrity Messages should not be modified in transit Nonrepudiation Sender should not be able to falsely deny sending a message later Confidentiality Intruders should not be able to learn sensitive data from a message Restricted Access Users should be able to perform requests that they are authorized to perform 12502 Doc 25 Security slide 3 Some Bad Ideas Security by Obscurity Security in the wrong place Authentication without checking Back doors 12502 Doc 25 Security slide 4 Security through Obscurity Security relies on encryptionauthentication methods are not obvious Some examples Reverse the byte order of a message Swap bytes in some quotsecretquot way Add garbage to data Use some quotsecretquot algorithm 12502 Doc 25 Security slide 5 Security in the Wrong Place Always think about what you39re trying to accomplish with a security system Examples Client performs authentication locally 12502 Doc 25 Security slide 6 Authentication without Checking A server that has an authentication and authorization should precede actions that require authentication Example POP server without the different states 12502 Doc 25 Security slide 7 Back doors Programmers have the tendency to add debug code to their servers to make testing easier This debug code may circumvent any security features of the server Example sendmail quotWIZARDquot Wizard command gave fu root privileges to the user The default distribution had this command enabled The quotInternet wormquot used this to attack machines throughout the Internet Sandia National Labs Security Agents Software Agent software based on Lisp Agents could perform any Lisp string Agents could request other agents to perform tasks Intruders could masquerade as an agent 12502 Doc 25 Security slide 8 General Security Issues Points to keep in mind when dealing with security The security system is only as strong as its weakest link A flawed security system is worse than no security at all US government considers encryption algorithms munitions Several security systems together are better than only one Security by obscurity really doesn39t work Some existing tools to look at PGP Pretty Good Privacy RCA Kerberos SSL Secure Socket Layer 12502 Doc 25 Security slide 9 Network Authentication Network packets can travel through many routers and computers The added risks Network sniffing Traffic logging Etc Some issues Passwords can be quotsniffedquot from the network Traffic patterns can be analyzed 12502 Doc 25 Security slide 10 Authentication methods Basic Username and password Protocols we have seen that use this POP HTTP Problems POP clients check for new mail periodically Each check requires sending namepassword HTTP requests are made every time a page is requested Username and password get embedding in html pages The more frequent the authentication information is sent over a network the higher the chance that it will be sniffed 12502 Doc 25 Security slide 11 OneWay Hash Functions Let M be a message sequence of bytes A oneway hash function f such that f maps arrays of bytes to arrays of bytes fM is always the same length Given an M it is easy to compute fM Given fM it is hard to compute M Given M it is hard to find N such that fM fN Common Oneway Hash Functions MD5 Message Digest 5 SHA Secure Hash Algorithm 12502 Doc 25 Security slide 12 OneWay Hash and Logins Let f be a oneway hash function Client has password and userName Client with IP XXX connects to server Client sends userName and fpassword XXX Server knows the client39s IP address XXX from the connection Server computes fpassword XXX to validate password Sniffer can only see fpassword XXX fpassword XXX only works from the machine with IP XXX 12502 Doc 25 Security slide 13 Using MD5 amp SHA in Java import javasecuIityMessageDigest import java secuIity NOSuChAl gorithmException public class SampleCOde public static void mainString args throws NOSuChAlgorithmException MessageDigest sha MessageDigestgetlnstancequotSHAquot shaupdate quot Hi momquot getBytesO byte shaHash shadigest System0utprintlnnew StringshaHash MessageDigest md5 MessageDigestgetlnstancequotMD5quot md5updatequotHi momquot getBytesO byte mdSHash md5digest System0utprintlnnew Stringmd5Hash 12502 Doc 25 Security slide 14 Using MD5 amp SHA in Smalltalk Load the MD5 amp SHA parcels 39Hi mom39 asByteArray md5Vaue 151963197781583193299119603806589129963 39Hi mom39 asByteArray shaValue 559954799469170145248016473141621 1 18947215272720 12502 Doc 25 Security slide 15 Using security tokens tickets or cookies Applications using stateless protocols http need to authenticate every request Server gives a security token to a client The token identifies the client to the server The client only authenticates once and thereafter uses the cookie The authentication may involve quotexpensivequot encryption Some requirements for security tokens Cannot be faked Need to expire after some time 12502 Doc 25 Security slide 16 Practical security token issues How can a server identify a client with a token Rely on a piece of information that is hard to change IP address of the client The server must correlate the token with the address when the client uses it How can this be done A random string which identifies the client A oneway scrambled string with client information 12502 Doc 25 Security slide 17 Encryption After a client has been authenticated the traffic on a network can still be sniffed A solution is encryption of all traffic This can be done at any layer of the protocol stack Two basic types of encryption Shared key encryption One key both encrypts and decrypts PublicPrivate key encryption One key encrypts another decrypts 12502 Doc 25 Security slide 18 PublicPrivate Key Encryption A public key is something that is well known ie published A client can send authentication information by encrypting the info with the server39s public key The server will then use its own private key to decrypt the information Advantages The information the client sends to the server cannot be decrypted by anyone except the server Common Algorithms RSA Rivist Shamir Adleman DSA Digital Signature Algorithm 12502 Doc 25 Security slide 19 RSA Public Key Key contains n amp e where n pq p amp q are primes e relatively prime to p1q1 p amp q must be kept secret Private Key d is e391 mod p1q1 that is de mod p1q1 1 Encrypting Let m be a message such that m lt n Let c be the encrypted message 0 me mod n If m gt n then break into block smaller than n and encrypt each block Decrypting mcdmodn 12502 Doc 25 Security slide 20 Example Example is from page 4678 of Schneier Alice s Keys La p47 q7l Then n pq 3337 e 79 Then d 79391 mod 3220 1019 So Alice s public key is n 3337 e 79 Alice s private key is d 1019 12502 Doc 25 Security slide 21 Sending a Message to Alice Let m 41 To send the message to Alice we compute c me mod n 4179 mod 3337 875 We send 875 to Alice Alice computes cd mode n 8571019 mod 3337 41 12502 Doc 25 Security slide 22 Digital Signatures The same encryption method can be used to authenticate a message A client encrypts information with its own private key The server will lookup the client39s public key and decrypt the information Advantages The information can only be decrypted with the client39s public key If the public key distribution center can be trusted the information is guaranteed to come from the client A combination of the previous two methods allows for mutual authentication 12502 Doc 25 Security slide 23 Alice Signing a Document Let the document be 67 Alice uses private key to compute 67d mode n 3081 To validate the signature anyone can compute 308 e mod n 308 79 mod 3337 67